DataWebTect

As usual Tom Kyte blogs about a feature in PL/SQL that many people are not aware of. It is a challenge to deal with the global variables and on top of that if a thing like Tom pointed out happens you would be scratching your head to find the answer. In order keep your globals straight he suggests,

The other approach, when FORCED to use globals, assign them to another variable OR cause a temporary to be created (|| ‘’ for strings, +0 for dates, numbers…)

Check out his post for more detailed information.

Very often in Oracle we use the packages to write jobs that load the data or read the data and do some manipulation and insert the data into the database. In order to debug the same we normally use the “DBMS_OUTPUT” which could sometimes becomes too unbearble and may throw out the “Buffer overflow” and will not provide the clear indication of where exactly the program is executing and what is the status etc.,

Andy Campbell gives this great tip, wherein he demonstrates the use of DBMS_APPLICATION_INFO to make the life of the DBA’s easier as well as keeping track of the long running application. In this he shows an example where he embeds the application info at various points and queries through V$SESSION and V$SESSION_LONGSOPS to nail out the execution points. Great stuff!

An interesting post by Tom Kyte on “When others” exception in PL/SQL.

“I see a when others then NULL. That is the worst thing a programmer could ever do. Basically you are saying “it does not matter if this code executes or not”. Well guess what - if it does not matter DO NOT EXECUTE IT ever!”

He is right and I have my personal experience to vouch it. Many programmers handle the “when others” which is a catch all for any type of exception by putting a null. Personally, I would like to use the when others to capture and inform what type of error it has captured like the code below

begin

…..

exception

When Others then

– either dbms out put or however you want to handle it.

dbms_output.put_line(”Unexpected error: “||substr(sqlerrm,1,250));

End;