Steven answers your questions about PL/SQL programming and best practices from a practical implementation point of view.

 Voir le site Best Practices PL/SQL with Steven Feuerstein: Most Recent Content

Use Oracle Virtual Private Database and Function Result Cache—Securely (Q&A)

We just upgraded to Oracle Database 11g, and I am excited about being able to use the new Function Result Cache feature. We also use Oracle Virtual Private Database (Oracle VPD) to restrict the rows in our tables that can be seen by our users. Can I use the Function Result Cache with an (...)

6 January 2010

Choose the Best Approach to Prevent a VALUE_ERROR Exception (Q&A)

If I try to use a FOR loop to iterate from FIRST to LAST and my collection is empty, PL/SQL raises a VALUE_ERROR exception. What’s the best way to avoid raising this error?

17 August 2009

First Things First (Q&A)

We are about to start construction of a brand-new PL/SQL-based application. What are the key best practices we should establish before we dive into writing the code for our next successful application rollout?

1 June 2009

Choose the Best Way to Manage Literal Values (Q&A)

What is the best way to avoid hard-coding literal "magic values" in my PL/SQL-based applications?

16 April 2009

Execute Host Command with DBMS_SCHEDULER, Java or C (Q&A)

How can I execute a host command from within a PL/SQL program?

24 March 2009

On Avoiding Termination (Q&A)

I have been assigned the job of modifying an existing procedure that applies a complex set of rules to a large volume of data in a set of tables. In the past, as soon as an error occurred in an update, the procedure would terminate execution. Now I need to change the procedure so that it (...)

2 March 2009

On Emulating FINALLY (Q&A)

I just moved over from the Java world to PL/SQL. One thing I really miss from Java is the FINALLY section of a method. How can I get the same behavior out of PL/SQL?

13 January 2009

On Saving Source Code (Q&A)

I just took a new job and discovered that the development team edits and saves our source code directly in the database. In all previous positions, we saved source code to files, and used the database as the platform testing and, of course, deployment. My new teammates argue that by storing (...)

5 November 2008

Determining Method Type (Q&A)

I have started working with object types and "mining" the data dictionary views for related information so I can generate useful reports. For the most part, the views are clear and helpful. I am stumped, however, when it comes to determining the kind of method found in the object type (static, (...)

24 October 2008

Qualifying All References (Q&A)

My DBA told me to put the names of my procedures and functions in front of any variables referenced inside SQL statements that I write in PL/SQL programs. This seems like a major hassle. Why should I bother with it?

24 October 2008

On Cursor FOR Loops (Q&A)

My mentor told me that when querying data I should always use a cursor FOR loop, even for a single row lookup. He says it’s the easiest way to fetch data, and Oracle Database automatically optimizes it in Oracle Database 10g and above. Do you recommend this (...)

24 October 2008

Changing WHERE (Q&A)

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 (...)

9 September 2008

Application Source Code Inside an Exception Handler (Q&A)

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 (...)

9 September 2008

Best practices for changing headers and handling different WHERE clauses (Q&A)

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. (...)

9 September 2008

Best practices for where, when, and how to handle exceptions (Q&A)

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 (...)

9 September 2008

Knowing your LIMIT (Q&A)

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 (...)

13 May 2008

Kicking the %NOTFOUND Habit (Q&A)

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 (...)

13 May 2008

Knowing your PGA impact (Q&A)

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?

13 May 2008

Indexing Collections (Q&A)

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, (...)

13 May 2008

How Do I Track My Songs? (Q&A)

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 (...)

19 October 2007

Best practices for PL/SQL in Oracle Database 11g and multilevel, string-indexed collections (Q&A)

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 (...)

19 October 2007

Best practices—and preparation—for PL/SQL in Oracle Database 11g (Q&A)

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) (...)

19 October 2007

Using subtypes to work with string-indexed collections more easily (Q&A)

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 (...)

23 August 2007

Never explicitly reference Oracle system error codes (Q&A)

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 (...)

23 August 2007

Generate a "backup trigger" for tables (Q&A)

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 (...)

23 August 2007

Best practices for invoker rights and functions (Q&A)

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 (...)

22 June 2007

No Way Out (Q&A)

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?

22 June 2007

An Error FORALL? (Q&A)

We have been just amazed at how much better our programs perform when we use FORALL to do our inserts and updates. We are now building a new application on Oracle Database 10g Release 2, and we have run into a problem. In all previous usages of FORALL, we would generally take a collection that (...)

25 April 2007

Table Encapsulation and %ROWTYPE (Q&A)

Steven, I have taken your advice about writing SQL statements to heart (don't write SQL in application-level code; hide them behind a packaged API, with as much of it generated as possible). I also decided (and I am the team leader so my decision carries some weight) to go the full route and I (...)

4 March 2007

The Right Place for PL/SQ (Q&A)

I write packages and procedures in both Oracle Database and Oracle Developer applications (Oracle Forms). How should I decide where to put my code?

16 January 2007

Best Practices for Managing Old and New Information (Q&A)

Hello Steven, My question is about Oracle Triggers. Here is the problem. My purpose is to audit updates so i want to store before and after value of any column of a specified table. In order to achieve this i used :new and :old structures with hard coded column names. But this cause (...)

8 December 2006

Working with Collections in Subprogram Headers (Q&A)

I have two questions: (1) How I can pass a collection as an argument to a procedure? and (2) How I return a collection from a procedure?

7 December 2006

Best Practices for Retrieving Objects (Q&A)

I have declared an object type, varray, that has three columns of datatype number, varchar2, and another object, respectively. How can I retrieve the third field (object type) from the varray?

27 October 2006

Retrieving Object Attributes from Objects (Q&A)

I have declared a nested object type table that has three columns of datatype number, varchar2, and another object, respectively. I then define a relational table with this nested table as a column. How can I retrieve an attribute of this object from a nested table in a row of the (...)

27 October 2006

Coding referential integrity? Say it ain't so! (Q&A)

I have "inherited" an application for which the backend was written in Sybase. Accorging to the original developers, all checking is done with triggers and indexes, not foreign keys. What are the advantages/disadvantages to this (...)

27 September 2006

Does a file exist? (Q&A)

Question: Is there a way to check to see if a system file exists in PL/SQL?

25 September 2006

Same names for variables, nested anonymous blocks = Confusion! (Q&A)

Question: How can I refer to anonymous block variables when I use the same name in inner and outer block? The following code block should give you a sense of what I want to do: Declare x pls_integer := 10; begin declare x number := 20.17; begin -- How do I refer to the outer block's variable (...)

25 September 2006

Who needs user-defined exceptions? (Q&A)

Oracle defines a number of exceptions for me, like no_data_found. Why would I ever need to or want to define my own exceptions?

25 September 2006

Best Practices for String Procedures and Tracing (Q&A)

What is the best way to write generic procedures to work on strings and/or lists of strings? For example, what is the best way to write a function to parse a delimited string into a collection, with a specific separator? I assume that function parameters should be declared as VARCHAR2(with no (...)

25 August 2006

What's the DBMS_OUTPUT Overhead? (Q&A)

What is the overhead involved in having DBMS_OUTPUT calls in production code where serveroutput is turned off? I've read about how to selectively call or compile code, but I can't find anything that says, "Yes, there is considerable overhead in making DBMS_OUTPUT calls; conditionally skip them (...)

25 August 2006

Where did my error go? (Q&A)

I am having trouble understanding how PL/SQL's exception raising and handling works. First, I wrote this code: DECLARE CURSOR c1 IS SELECT sal, comm FROM emp; ratio NUMBER; BEGIN FOR r1 IN c1 LOOP ratio := r1.sal / NVL ( r1.comm, 1 ); IF ( ratio And when ratio is more than 1, (...)

26 May 2006

How best to return multiple pieces of data from a subprogram? (Q&A)

I need to call a program and return a number and a date. I usually write a function to return information. Can a function return two different values?

23 May 2006

Dynamic IN clauses and PL/SQL (Q&A)

I am using an IN clause in my query to specify a subset of rows. The users just changed the requirements so that now I need to supply the values for the IN clause at runtime. There could be 1, 2 or 100 strings (or numbers or dates...) in the list. How can I implement a dynamic IN clause in (...)

8 May 2006

Externalizing internal errors for external tables (Q&A)

I'm working with external tables in PL/SQL, and I want to handle exceptions dealing with all the things that can go wrong with external tables: file missing, permissions, etc. However, the top-level error for all external table errors is: "ORA-29913: error in executing ODCIEXTTABLEOPEN (...)

27 March 2006

Finding dynamic SQL placeholders in a string (Q&A)

I am using dynamic SQL to execute various PL/SQL blocks that are stored as strings in rows in a database table. I need to figure out how many placeholders for bind variables appear in those blocks, and their names. Does Oracle provide a utility that returns this (...)

27 March 2006

Tracing when you need it and how you need it (Q&A)

I just started working at a new position, enhancing an application that's been around for several years. I was alarmed to find that the team was relying on very primitive debugging and tracing approaches, basically inserting calls to DBMS_OUTPUT.PUT_LINE or a "log to table" program all over the (...)

24 March 2006

Testing Private Subprogram Units (Q&A)

I use packages extensively to implement my complex application requirements, and rely heavily on private subprograms in the package body to maximum code reuse and hide information not needed by a user of the package. I also want to be able to unit test those "hidden" procedures and functions, (...)

24 March 2006

FORALL and Every Version of PL/SQL (Q&A)

I love the FORALL feature that Oracle added to PL/SQL in Oracle8i Database! I use it whenever possible to implement high-speed DML processing. I am also writing code that must work on Oracle9i Database and Oracle Database 10g. I was very excited to see that Oracle Database 10g offers the (...)

24 March 2006

Get It Right with the Error Directive (Q&A)

I often find myself working on multiple program units, fixing some and developing others anew. As I move around between them, I sometimes have to leave work half finished. What do you think is the best way to indicate that this program unit is incomplete and keep track of what needs to be (...)

24 March 2006

Conditional Compilation in PL/SQL (Q&A)

What is PL/SQL conditional compilation, how does it work, and how can I learn more about it?

24 March 2006

Meaningful or Cuddly? (Q&A)

What are your thoughts about how much information should be displayed to application users when an exception occurs? Say a record can't be created because of a primary key constraint—should users see the "ORA-00001...," so they can give a meaningful message to Support to help track the (...)

15 March 2006

Best way to tell if row exists? (Q&A)

What is the best approach to determining whether or not a row exists in a table? Currently I have implemented two different methods: 1. Do a SELECT count(*) INTO lv_tester FROM table. Then IF lv_tester > 0 THEN Some Logic.... ELSE RAISE error END IF; 2. Use a Cursor Loop and a local variable to (...)

13 March 2006

Recompiling invalid program units (Q&A)

How can I recompile all invalid program units in my schema?

16 February 2006

Dealing with errors in assigning values to packaged constants (Q&A)

We define constant variables in our package specifications. If there should occur an error, which exception handler would take care of that?

15 February 2006

Please read! New URL for "Best Practice PL/SQL" RSS feed (Q&A)

The URL for this RSS feed has changed to http://apex.oracle.com/pls/otn/asksteven.recent.rss. Please update your newsreader or live bookmarks accordingly.

30 January 2006

Which collection type should I use? (Q&A)

I need to pass a collection from one PL/SQL program to another. Which collection type should I use, or does it not make any difference?

18 January 2006

Obtaining the names of columns in a dynamic SELECT (Q&A)

I store SELECT statements in a relational table, and then retrieve and execute them dynamically in PL/SQL, as specified by the user. I need to obtain the names of the columns in my queries, to use in the display of the headers. How can I get this (...)

16 January 2006

Reversing a String (Q&A)

I need a function that will return the reverse of a string. In other words, if I pass in "hello", I want to receive back "olleh." Does PL/SQL have a built-in function to do this for me?

15 January 2006

If implicit cursors are always closed, how can the SQL%ROWCOUNT attribute tell me anything useful? (Q&A)

When I use an implicit cursor, Oracle opens, parses, executes and eventually closes that cursor for me – implicitly. So how is it possible that I can get information about that closed cursor through SQL%ROWCOUNT?

12 January 2006

Avoiding program name confusion: schema-level and packaged (Q&A)

I have a package named pck_events_1 which has a function named fn_1. I have another schema-level (aka, "stand-alone") function, also named fn_1, defined in the same schema. How can I call the schema-level function from a subprogram inside the (...)

9 January 2006

Should I use SELECT INTO when I need to query a single row of data, or declare an explicit cursor and OPEN-FETCH-CLOSE? (Q&A)

What are advantages and disadvantages of a SELECT INTO statement?

5 January 2006

What Loop with BULK COLLECT collections? (Q&A)

I noticed in your answer to a question regarding PL/SQL support for bi-directional cursors that you used BULK COLLECT to fill a collection, and then iterated through the contents of that collection using a WHILE loop, and the FIRST and NEXT methods. As I am sure you know, BULK COLLECT queries (...)

20 December 2005

Defining a two-dimensional array of numbers (Q&A)

How can I define a two-dimensional array of numbers in PL/SQL?

20 December 2005

Stripping out unwanted characters from a string variable (Q&A)

I'm using Oracle9i Database. How can I strip out unwanted characters from a string variable? For example, I would like to be able to call a function named stripped_string like this: DECLARE l_before VARCHAR2 (100) := 'This is my string'; l_after VARCHAR2 (100); BEGIN l_after := (...)

20 December 2005

Ways to hide your code (Q&A)

I want to allow everyone to run my procedures and functions, but I don't want them to be able to see the code behind the program header. How can I accomplish that?

6 December 2005

Calculate elapsed time? Use an interval! (Q&A)

I want to write a function that tells me the gap or period of elapsed time between the end of one timesheet record and the beginning of another. For example: Start End 05-DEC-05 08:30 05-DEC-05 16:29 06-DEC-05 08:30 06-DEC-05 16:29 Is it better to return a date or a period of elapsed (...)

5 December 2005

Use LIMIT to manage memory as you fetch (Q&A)

I need to query more than 100,000 rows from a table into my PL/SQL program. I want to use BULK COLLECT and from a memory consumption standpoint can only afford to populate a collection with 5000 rows at a time. I know that I should use the LIMIT clause, like this: FETCH my_cursor BULK COLLECT (...)

29 November 2005

Are the gaps in my collection disappearing? (Q&A)

The PL/SQL User's Guide states that if a PL/SQL programs attempts to reference an undefined row in a collection, Oracle raises the NO_DATA_FOUND exceptions. However, I have found that if a collection with "gaps" is passed to a procedure via EXECUTE IMMEDIATE, Oracle is able to read through all (...)

24 October 2005

Do I really need to learn the syntax for BULK COLLECT and FORALL? (Q&A)

I attended your seminar "You Wrote What?" in London, which was great by the way, and noted the emphasis you placed on using bulk binding to enhance performance. In particular you explained the trade-off between more complex code and performance. I have just read an article by Bryn Llewellyn in (...)

24 October 2005

Should we show our users cuddly messages? Draft to Bryn on 10/25/05 (Q&A)

What are your thoughts about how much information should be displayed to application users when an exception occurs? For example, in the case of a record that can't be created because of a PK constraint, should the user see the "ORA-00001... " stuff, so they can give a meaningful message to (...)

24 October 2005

Immediate termination of a procedure (Q&A)

How do I exit immediately from a procedure?

14 October 2005

11/5 - draft to bryn Reverse a string (Q&A)

Does PL/SQL have a function to reverse a string? e.g. I hve a string "hello". I want to reverse it to "olleh". The string could be anything.

14 October 2005

Coding standards for PL/SQL (Q&A)

I would like to follow standards as I write my PL/SQL programs. Can you suggest coding standards for the Oracle PL/SQL language? Does Oracle have a set of recommended standards?

3 October 2005

Calling an operating system command from within PL/SQL (Q&A)

For years, the only way to execute an operating system command was to use DBMS_PIPE to "pipe" out the command to an operating system shell script. Is there now an easier way to do this in PL/SQL?

29 September 2005

SQL or PL/SQL when performing DML on lots of rows? (Q&A)

It's my understanding that the primary benefit of using the FORALL statement rather than the FOR loop is that the number of context switches between SQL and PL/SQL is minimized. Is this correct? I prefer whenever possible (and it almost always is) to incorporate the logic of the PL/SQL block (...)

31 August 2005

Is a string a valid number? (Q&A)

Does PL/SQL offer an is_number function (like isNumber in VB) that will tell me whether a string is a valid number?

20 August 2005

Referencing elements in multi-level collections (Q&A)

I'm having a tough time working with multilevel collections. Specifically, given this set of definitions: CREATE TYPE info_ot AS OBJECT ( info VARCHAR2 (100) ,entered_on DATE ) / CREATE TYPE notes_nt AS TABLE OF info_ot / CREATE TYPE account_ot AS OBJECT ( NAME VARCHAR2 (100) ,notes notes_nt ) (...)

11 August 2005

No more WHERE CURRENT OF? (Q&A)

I have a question for you regarding BULK COLLECT. I have a program that defines a CURSOR using FOR UPDATE OF col1. I then run a cursor FOR loop against that cursor and perform an UPDATE against the table using the WHERE CURRENT OF syntax. I would like to switch to a BULK COLLECT to query the (...)

25 July 2005

Semi-colons and SQL (Q&A)

Dear PL/SQL Product Manager: The PL/SQL User Guide and Reference section entitled "Guidelines for Dynamic SQL" tells us that "When building up a single SQL statement in a string, do not include any semi-colon at the end. When building up a PL/SQL anonymous block, include the semi-colon at the (...)

13 July 2005

The DIRECTORY Object: Handy for BFILEs and UTL_FILE (Q&A)

What is the purpose of the DIRECTORY object in Oracle?

13 July 2005

Should I explicitly assign a default value of null? (Q&A)

I have some questions about variable initialization. PL/SQL, by default, initializes all variables to null. Thus: (A) l_variable varchar2(10); is equivalent to: (B) l_variable varchar2(10) := null; Suppose that 100 users are running 100 to 200 procedures, eight hours a day. Furthermore, (...)

13 July 2005

Recommendations for Error Handling? (Q&A)

What is the best way to write robust exception and error codes in PL/SQL procedures and functions? I've seen this done in various ways, but there must be a better one!

13 July 2005

Implementing "Global" Collection Types (Q&A)

I have some packages with some functions and procedures in each one of them: package1: function1; function2; procedure1; package2: function1; function2; procedure1; My problem is that all these packages are using a function to log something placed in another package: package3: functionlog; Now, (...)

13 July 2005

Why no CONTINUE command in PL/SQL? (Q&A)

Is there any CONTINUE command in PL/SQL (the counterpart of break, which exits from a loop if a condition is met) like the one in the C programming language?

13 July 2005

Dynamic IN Clause with Native Dynamic SQL (Q&A)

Hi Steven: How can I use a parameter that has multiple values in the IN clause for a column? For example, p_id is the parameter for a proc and it has the value of 1,2,3. I want to use this p_id parameter in select * from a_table where id in p_id. Can I do (...)

13 July 2005

BULK COLLECT and FOR UPDATE OF: A Fine Match (Q&A)

Can I bulk collect using the FOR UPDATE OF clause in a SELECT statement? For example: SELECT * BULK COLLECT INTO l_employee FROM employee FOR UPDATE OF employee ORDER BY last_name Or in other words: I want to ensure that nobody updates the selected records while I run the BULK COLLECT process. (...)

13 July 2005

Execute REALLY BIG SQL statements with DBMS_SQL (Q&A)

I have been assigned the task of automating a data warehouse load procedure with PL/SQL. I'm having trouble with one part: creating views. We have some procedures that read link tables and from them generate the DDL to create views. The DDL is written to OS files via UTL_FILE. They are then (...)

13 July 2005

SQL%ROWCOUNT in cursor FOR loop? Sure, for all the good it will do you... (Q&A)

How do I access the %ROWCOUNT for an implicit cursor in a cursor FOR loop, as in (select * from dept) loop?

13 July 2005

Aborting execution across the stack (Q&A)

How can I abort the execution of my entire call stack in PL/SQL? In other words, program A calls program B, etc., and way down deep in my call stack, a exception occurs that is so nasty that I want it to stop processing all the way up the stack, regardless of what the enclosing blocks' (...)

29 June 2005

I Take Exception to Your Exceptions (Puzzler)

In this puzzler, we take a break from writing a program to solve a problem, and instead shift to code review and best practices. Error handling is a critical element of any well-built application, but it can also be a tricky thing to get right—and italso be be misused. In each of the (...)

2 June 2005

Name of currently-executing program? (Q&A)

When I trap an error in an exception section, I want to record as part of my error log the name of the subprogram currently being executed. I would rather not hard-code the name in a local variable. How can I do that in PL/SQL?

13 May 2005