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?
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?
My understanding is that a function should send back data only through its return clause. Why does PL/SQL allow us to define OUT parameters with functions? Is there any specific application of this feature?
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.
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?
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?
I have made lots of use of the AUTHID CURRENT_USER (invoker rights) clause lately. I frequently write utilities for other developers on my team, and I define them in a central schema named SHARED_CODE. When I define a program with invoker rights, all developers can call that program and it will automatically perform the desired operations on that developer’s own schema objects. But recently I ran into a problem with invoker rights. On our next project, we are going to be working a lot with operating system files via UTL_FILE. In particular, we will be writing text out to files, and that text is going to be formatted in various different ways. So I decided I would write a “write to file” engine that would do all the UTL_FILE operations but let the users specify the formatting by calling their own functions. That way they would have maximum flexibility to do that formatting. But it doesn’t seem to work. I define my “write to file” program with invoker rights, but it refuses to call the invoker’s formatting routine. What am I doing wrong?
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
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
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;