Summer is coming to an end, but the sun is still shining in Chicago and here I am, at my desk, staring at a computer screen. Ah, well. I will finish up these tips and then go out for a run. I encourage you to do the same (that is, find a healthy balance between typing on a keyboard and exercising the rest of your body).
It's hard when you feel terribly pressured to churn out code to make time to learn about the ever-changing elements of a programming language, even one as relatively stable as PL/SQL.
So I offer a couple of quick tips that might help improve the robustness of your programs and your ability to diagnose problems (and find solutions to them) more quickly.
Warm regards,
Steven
Tip 1. Don't 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 (at least, that seems to have been the original motivation for the truncation).
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:
- It can get very large, so don't try to display it with DBMS_OUTPUT.PUT_LINE unless you are on Oracle Database 10g 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.
Web-based Trainings on PL/SQL - Just for You!
Of course, there are lots more to PL/SQL than learning about a couple of very interesting built-in functions.
How do you learn about all the other stuff? The best way is to attend a training by someone who obsesses over that programming language.
Leaving work these days for training is a tough sell; budgets are very tight. That's why Quest and I have decided to organize a series of inexpensive web-based trainings on key aspects of PL/SQL.
From the convenience (and comfort?) of your own desk, you can join me for a total of six hours of intensive education in three areas:
I hope you can join me!