Best Practices PL/SQL

Knowing your LIMIT (Q&A)
Databases referred: Oracle Application Express
Technologies referred: Application Express SQL & PL/SQL
I have started using BULK COLLECT whenever I need to fetch large volumes of data. This has caused me some trouble with my DBA, however. He is complaining that although my programs might be running much faster, they are also consuming way too much memory. He refuses to approve them for a production rollout. What's a programmer to do?
2008-05-12

Kicking the %NOTFOUND Habit (Q&A)
Databases referred: Oracle Application Express
Technologies referred: Application Express SQL & PL/SQL
I was very happy to learn that Oracle Database 10g will automatically optimize my cursor FOR loops to perform at speeds comparable to BULK COLLECT. Unfortunately, my company is still running on Oracle9i Database, so I have started converting my cursor FOR loops to BULK COLLECTs. I have run into a problem: I am using a LIMIT of 100, and my query retrieves a total of 227 rows, but my program processes only 200 of them. [The query is shown in Listing 2.] What am I doing wrong?
2008-05-12

Knowing your PGA impact (Q&A)
Databases referred: Oracle Application Express
Technologies referred: Application Express SQL & PL/SQL
My DBA wants me to reduce the amount of PGA (program global area) memory I use in my collection-based programs. Isn't it the DBA's job to manage memory, and if it isn't, how am I supposed to know how much PGA memory I am using?
2008-05-12

Indexing Collections (Q&A)
Databases referred: Oracle Application Express
Technologies referred: Application Express Performance SQL & PL/SQL
I want to use associative arrays to quickly look up an office product name for a given product number and a product number for a given product name. Product names are unique, and product numbers are integers. I see how I can use the product number as the index value in my collection of names, but I also need to reverse the process and find a product number for a given product name. Can I create another index on a collection's contents?
2008-05-12

Best practices—and preparation—for PL/SQL in Oracle Database 11g (Q&A)
Databases referred: Oracle Application Express Oracle Application Express
Technologies referred: Java Java Application Express Application Express Performance Performance SQL & PL/SQL SQL & PL/SQL
I have been reading that Oracle is launching the 11th release of its database. Very exciting! But here's the problem: I don't think I'll be able to use it for another two years. So why should I even care about the new PL/SQL features of this future (for me) release?
2007-10-18

Best practices for PL/SQL in Oracle Database 11g and multilevel, string-indexed collections (Q&A)
Databases referred: Oracle Application Express Oracle Application Express
Technologies referred: Application Express Application Express SQL & PL/SQL SQL & PL/SQL
I have been assigned one of those big blobs of spaghetti code to maintain, and in particular I have to make changes to a very complicated loop. I want to be able to make a "surgical strike"—put the new rules in place and then bypass the rest of the logic in the loop body with the minimum-possible fuss. What's the best way to do this?
2007-10-18

How Do I Track My Songs? (Q&A)
Databases referred: Oracle Application Express Oracle Application Express
Technologies referred: Application Express Application Express SQL & PL/SQL SQL & PL/SQL
I work for a radio station (call it WORA—not the real name), and I need to write a program that keeps track of how many times a song is requested and played within a given period and also track the count of songs in one of our two categories: folk and rock (I am simplifying things for the question). Although the list of available songs is stored in a database table, this tracking information is not stored in the database; it is active only during the current session. I see how I could write the program by creating a few database tables and writing a bunch of SQL, but I wonder if there might not be an easier way.
2007-10-18

Using subtypes to work with string-indexed collections more easily (Q&A)
Databases referred: Oracle Application Express Oracle Application Express
Technologies referred: Application Express Application Express SQL & PL/SQL SQL & PL/SQL

I have started working with multi-level and string-indexed collections. I very much like the way these features can simply the code I need to write to manipulate complex structures. But sometimes I get really confused trying to remember which data is used in which index. For example, in one program, I needed to query and keep track of some key employee information by department, using the names instead of numbers for indexes (in our company, last names of employees are unique!). So I wrote the code below, but got really confused when it was time to assign the record to the right place in my complex collection. (see "?????"). I finally figured it out, but I wonder if there is something I could change in my code to avoid the confusion. —Alejandra

DECLARE
   TYPE key_info_rt IS RECORD (
      hire_date   employees.hire_date%TYPE
    , salary      employees.salary%TYPE );

   l_emp_rec    key_info_rt;

   TYPE employees_t IS TABLE OF key_info_rt
      INDEX BY VARCHAR2 (100);

   TYPE emps_in_department_t IS TABLE OF employees_t
      INDEX BY VARCHAR2 (100);

   l_emp_info   emps_in_department_t;
BEGIN
   FOR temp_rec IN (SELECT d.department_name, e.last_name, e.hire_date
                         , e.salary
                      FROM departments d, employees e
                     WHERE d.department_id = e.department_id)
   LOOP
      l_emp_rec.hire_date := temp_rec.hire_date;
      l_emp_rec.salary := temp_rec.salary;
      /*
      Now I need to put the record into the right location in my 
      two-level collection. But which name do I put where?
      */
      ?????
   END LOOP;
END;

Never explicitly reference Oracle system error codes (Q&A)
Databases referred: Oracle Application Express Oracle Application Express
Technologies referred: Application Express Application Express SQL & PL/SQL SQL & PL/SQL

I was recently given responsibility to make a change to a program built several years ago by a consultant. She used FORALL to perform a massive set of inserts, and included the SAVE EXCEPTIONS clause so we could as many of the inserts completed as possible. That all made sense to me. But then I looked at the exception section and got really confused. It contained this code:

EXCEPTION
   WHEN OTHERS
   THEN
      IF SQLCODE - 24381
      THEN
         ...

I was able to figure out what was going on—after a while. But I'd really like to change it so that the next person working on this code doesn't have to do the same detective work. What should I do with this code?—Josef

Generate a "backup trigger" for tables (Q&A)
Databases referred: Oracle Application Express Oracle Application Express
Technologies referred: Application Express Application Express SQL & PL/SQL SQL & PL/SQL

I need to implement a "backup trigger" on all of the tables in my application, so that whenever anyone changes the data in one of the tables, the existing data is copied to the backup table. I really, really don't want to have to write these myself for 100 tables. What is the best (you know what I mean: easiest) way to do that?. —Andy