New Logo
Steve Feuerstein
Greetings!
 
I am writing this mini-newsletter to you for three reasons:
  1. To provide two tips on how to better take advantage of PL/SQL
  2. To extend an invitation to join me on April 21-22 in Salt Lake City, when I will be presenting a two day training seminar sponsored by TruTek, titled The Best of Oracle PL/SQL.
  3. To let you know that TruTek is offering a 10% Early Bird Discount if you sign up for the class by March 25th.
Best of Oracle PL/SQL: Hope to see you there!

Steve Feuerstein
First, a Tip or Two...

Tip 1. Never use SQLERRM to get the current error message

Hmmm. "That's strange", you might be thinking. "Isn't that precisely what SQLERRM returns?" Well, actually, it is a general lookup function for Oracle messages, as you can see below:

SQL> BEGIN
  2     DBMS_OUTPUT.put_line (SQLERRM (-1855));
  3  END;
  4  /

ORA-01855: AM/A.M. or PM/P.M. required

Here's the problem: if the error message gets too long, Oracle may truncate it so that it can be displayed by DBMS_OUTPUT.PUT_LINE.

That's not very nice.

So instead, Oracle recommends that you call the DBMS_UTILITY.FORMAT_ERROR_STACK function. Occasionally, it will actually return a stack of error messages, but usually it simply returns the error message of the currently-raised error. Plus, it will never truncate that message!

Tip 2. Call DBMS_UTILITY.FORMAT_CALL_STACK to show the execution call stack

This function can be called from any location in your code, and it will return a formatted string containing the "call stack" - the trace of subprogram calls that shows how you got to that point in your code.

The call stack is critical information whenever you are logging or tracing activity in your application code.

Here is an example (rather trivial) of using this function:

SQL> CREATE OR REPLACE PROCEDURE proc1
  2  IS
  3  BEGIN
  4     DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack);
  5  END;
  6  /
 
SQL> CREATE OR REPLACE PROCEDURE proc2
  2  IS
  3  BEGIN
  4     proc1;
  5  END;
  6  /
 
SQL> CREATE OR REPLACE PROCEDURE proc3
  2  IS
  3  BEGIN
  4     proc2;
  5  END;
  6  /
 
Procedure created.
 
SQL> exec proc3;
 
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
24361F20         4  procedure HR.PROC1
29CF10E4         4  procedure HR.PROC2
24394FCC         4  procedure HR.PROC3
24397364         1  anonymous block

There are two things to keep in mind about DBMS_UTILITY.format_call_stack:
  1. It can get very large, so don't try to display it with DBMS_OUTPUT.PUT_LINE unless you are on Oracle Database 11g Release 2, otherwise you will raise a VALUE_ERROR exception.
  2. If you are running subprograms in a package, then the call stack will only show you the name of the package, but not the name of the actual procedure or function in the package that is being run.
Best of Oracle PL/SQL: Hope to see you there!
The Best of Oracle PL/SQL Seminar - Steve Feuerstein
Over its lifetime, PL/SQL has grown increasingly robust and complex. Many programmers are barely aware of the new capabilities of  PL/SQL, much less fully understand the implications for their programs. And virtually all PL/SQL developers are too pressured by deadlines to have the time to think through the best way to apply PL/SQL's many features.

In this two-day seminar I teach attendees about the most important features of the PL/SQL language, focusing on the major advances in Oracle Database 10g and Oracle Database 11g. I then take you beyond the basics to show you how to apply these features in the context of best practices, so that you can write highly optimized and easily maintained and enhanced applications.

After attending this course, you will be much better situation to take full advantage of PL/SQL and do so in a way that results in much higher quality code. I hope that you can join me. Don't hesitate to get in touch (mailto: steven@stevenfeuerstein.com) if you have any questions regarding this seminar or about PL/SQL in general.

After attending this seminar, you will be ready and able to:
  • Improve query and DML performance by an order of magnitude or more with BULK COLLECT, FORALL and PL/SQL collections (array-like structures).
  • Unit test your PL/SQL subprograms like never before, utilizing the processes and tools Steven offers in his "six simple steps to unit testing happiness."
  • Optimize the construction of your SQL statements in PL/SQL programs, both from the standpoint of performance and maintainability.
  • Build modular, reusable code: Steven will show you how to take advantage of packages, local subprogram units, cursor variables, autonomous transactions, and more to craft small, reusable units of code.
  • Manage errors by following a standard, consistent process for handling, raising, and reporting errors. Apply collections (a critical but under-utilized data structure) to solve a wide variety of problems, from slow query performance to mutating table trigger errors.
  • Take advantage of both forms of dynamic SQL in PL/SQL to write the kind of flexible programs that are required in a world driven by the Internet.

Steve Feuerstein

Oracle Ace Director, Steven Feuerstein is one of the world's leading experts on the Oracle PL/SQL language, having written nine books on PL/SQL, including "Oracle PL/SQL Programming" and "Oracle PL/SQL Best Practices." Steven has been developing software since 1980, spent five years with Oracle (1987-1992) and serves as a Senior Technology Advisor for Quest Software. His Oracle PL/SQL Best Practices column is one of the most popular pages on the Oracle Technology Network, and he writes regularly for Oracle Magazine and Oracle Professional. 
Upcoming Training Schedule
 

Click here for the complete TruTek Training Schedule!
Save 10% Register by March 25th to receive a 10% Early Bird Discount!
Offer Expires: March 25th!