Time for the June 2005 Oracle PL/SQL Programming e-newsletter. Summer approacheth and I hope you all have some relaxing time planned -- away from your laptops, desktops, Oracle, PL/SQL and other elements of your digital world. Before that happens, though, we have the annual ODTUG (Oracle Developer Tools User Group) conference in New Orleans. Hope to see you there! You will find below a Tip of the Month, Useful Code of the Month, Recent Writings, Upcoming Public Trainings, and a new section: Help Wanted. And if you haven't yet heard about or checked out the following, please take a peek:

Oracle PL/SQL Programming 2005 - a two-day PL/SQL conference to be held on November 2-3, 2005 in Chicago.  Featuring in-depth presentations by Steven Feuerstein (don't you love it when people mention themselves in the third person?), Bryn Llewellyn (PL/SQL Product Manager) and Marchel Kratochvil (Winner of the 2004 Oracle Magazine PL/SQL Developer of the Year award), OPP2005 is a prime learning opportunity for PL/SQL developers.  It will also be a birthday party: we will be celebrating the 10th anniversary of the publication of Oracle PL/SQL Programming! Space is limited, so I encourage you to register early.

21st Century PL/SQL Idea Collector - PL/SQL is a mature, robust language, but that doesn't we don't each have our own list of things we'd like to see added or changed in PL/SQL to make it even better.  Now you have a way to share your ideas with other developers and with the PL/SQL development team.  The 21st Century PL/SQL Idea Collector allows you to submit suggestions and rank the ideas of others.  These recommendations will then be presented to Bryn Llewellyn at the Oracle PL/SQL Programming 2005 conference.

Tip of the Month

When querying multiple rows of data from Oracle, don't use the cursor FOR loop. Instead, assuming you are running at least Oracle8i, start using the wonderful, amazing BULK COLLECT query, which improve query response time very dramatically. The following statement, for example, retrieves all the rows in the employee table and deposits them directly into a collection of records:

DECLARE
   TYPE employee_aat IS TABLE OF employee%ROWTYPE
      INDEX BY BINARY_INTEGER;

   l_employees employee_aat;
BEGIN
   SELECT *
     BULK COLLECT INTO l_employees
     FROM employee;
END;

DECLARE
    TYPE employee_aat IS TABLE OF employee%ROWTYPE
       INDEX BY BINARY_INTEGER;

    l_employees employee_aat;

    CURSOR employees_cur IS SELECT * FROM employee;
BEGIN
    OPEN employees_cur;
    LOOP
        FETCH employees_cur 
         BULK COLLECT INTO l_employees LIMIT 100;

        EXIT WHEN l_employees.COUNT = 0;
        -- Process these 100 rows and then
        -- move on to next 100.
    END LOOP;
END;