I am writing this mini-newsletter to you for three reasons:
Best of Oracle PL/SQL:
Hope to see you there!
provide two tips on how to better take advantage of PL/SQL
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.
- To let you know that TruTek is offering a 10% Early Bird Discount if you sign up for the class by March 25th.
||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:
2 DBMS_OUTPUT.put_line (SQLERRM (-1855));
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
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
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
4 DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack);
SQL> CREATE OR REPLACE PROCEDURE proc2
SQL> CREATE OR REPLACE PROCEDURE proc3
SQL> exec proc3;
----- PL/SQL Call Stack -----
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
Best of Oracle PL/SQL:
Hope to see you there!
- 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.
- 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.
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: firstname.lastname@example.org)
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.
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|