I need to write a procedure to process multiple rows of data from a table, and each time I call the procedure, the WHERE clause may change. I would like to use EXECUTE IMMEDIATE, but that lets me return only a single row of data. How can I avoid the nightmare of maintaining code in multiple procedures that is completely identical except for the WHERE clause?
I maintain a large application implemented in PL/SQL. Lately, a number of enhancements have required changes to the signatures of several procedures and functions. I have had to add new parameters and remove others. This has led to the need to change many other programs that call these units. How can I minimize the ripple effect of changes such as these?
I was taught that it is a bad practice to put application source code inside an exception handler. We should be able to remove all our exception sections, and—assuming no errors—our code should work the same. But I’ve run into lots of situations where I execute a SELECT INTO (an implicit one-row query), expecting it to return no rows (in other words, that’s the correct result). However, Oracle Database raises a NO_DATA_FOUND exception, and I then have to write application logic down in the exception section. Should I never use an implicit SELECT INTO statement in my code?
I recently learned that if an exception is raised in the declaration section of my block, that block’s exception section cannot handle the exception. That doesn’t seem right. Why does PL/SQL work this way, and what does it mean for my coding practices?
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?
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?
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.