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, (...)
Best Practices PL/SQL with Steven Feuerstein : Most Recent Content
Steven answers your questions about PL/SQL programming and best practices from a practical implementation point of view.
6 janvier 2010 : 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 (...)
17 août 2009 : 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?
1er juin 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?
16 avril 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?
24 mars 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?
2 mars 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 (...)
13 janvier 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?
5 novembre 2008 : 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 (...)
24 octobre 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 octobre 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 octobre 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 (...)
9 septembre 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 septembre 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 septembre 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 septembre 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 (...)
13 mai 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 mai 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 mai 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 mai 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, (...)
19 octobre 2007 : 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 octobre 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 octobre 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) (...)
23 août 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 août 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 août 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 (...)
22 juin 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 juin 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?
25 avril 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 (...)
4 mars 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 (...)
16 janvier 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?
8 décembre 2006 : 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 (...)
7 décembre 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?
27 octobre 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 octobre 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 septembre 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 (...)
25 septembre 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 septembre 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 septembre 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 août 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 août 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 (...)
26 mai 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, (...)
23 mai 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?
8 mai 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 (...)
27 mars 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 mars 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 (...)
24 mars 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 mars 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 mars 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 mars 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 mars 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?
15 mars 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 (...)
13 mars 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 (...)
16 février 2006 : Recompiling invalid program units (Q&A)
How can I recompile all invalid program units in my schema?
15 février 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?
30 janvier 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.
18 janvier 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?
16 janvier 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 (...)
15 janvier 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?
12 janvier 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?
9 janvier 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 (...)
5 janvier 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?
20 décembre 2005 : 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 décembre 2005 : Defining a two-dimensional array of numbers (Q&A)
How can I define a two-dimensional array of numbers in PL/SQL?
20 décembre 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 := (...)
6 décembre 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?
5 décembre 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 (...)
29 novembre 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 (...)
24 octobre 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 octobre 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 octobre 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 (...)
14 octobre 2005 : Immediate termination of a procedure (Q&A)
How do I exit immediately from a procedure?
14 octobre 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.
3 octobre 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?
29 septembre 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?
31 août 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 (...)
20 août 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?
11 août 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 ) (...)
25 juillet 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 (...)
13 juillet 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 juillet 2005 : The DIRECTORY Object: Handy for BFILEs and UTL_FILE (Q&A)
What is the purpose of the DIRECTORY object in Oracle?
13 juillet 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 juillet 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 juillet 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 juillet 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 juillet 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 juillet 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 juillet 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 juillet 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?
29 juin 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' (...)
2 juin 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 (...)
13 mai 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?
