pl/SQL Logging
During a batch process or the execution of pl/SQL, you will want to log the output and the success or failure of the process.
Typically, I always find it good practice to output some simple information such as the start and end date/time of the process as well as any errors that may occur. This is really handy if you are wanting to look at how long a process takes and will give you a feel for if something ‘went wrong’ while running the script.
If you run a script as a one off, you will see the output of your code (package, procedure, anonomous block) in the output from your IDE such as TOAD, SQL developer or SQL*Plus.
Example:
set serveroutput on ... your code
or you can redirect it to a file (see below)
Example:
set serveroutput on spool log.txt ... your code spool off
If you run a batch process using crontab or some other scheduler, use this to redirect your output into a file. If you do this, it is always a good idea to time stamp the file.
Exception handling
In your batch pl/SQL applications, you will need to have some exception handling.
If anything, you will want to catch some specific errors as well as others.
In my examples below, I am catching a NO_DATA_FOUND exception and outputting a different error to when any other exception occurs.
Example:
BEGIN DBMS_OUTPUT.PUT_LINE('Batch process started - '||SYSDATE); ... your pl/SQL block that does stuff... COMMIT; --commit changes DBMS_OUTPUT.PUT_LINE('Batch process ended - '||SYSDATE); EXCEPTION WHEN NO_DATA_FOUND THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('No data was found - '||SQLCODE||' -ERROR- '||SQLERRM); EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); END;
Raising your own exceptions
Another option is when you come across a condition in your code where you want to throw an exception. A really good example of this is if a variable ends up with a negative number you may want to raise an exception rather than let the program continue on.
Example:
BEGIN IF input > 0 THEN ... your pl/SQL block that does stuff... ELSIF input = -1 --Raise no data found exception if RAISE NO_DATA_FOUND; ELSE --Something wrong has happened. Raise an exception. RAISE; END IF: EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Please enter a correct paramater and try again.'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); END; END;
Creating your own custom exceptions
It is also possible to create your own exceptions and even allocate them to their own error number within the user-specified range of between -20000 – -20999. All other error numbers are reallocated to standard oracle errors. While this is certainly possible, I’ve never found a real life scenario where I have wanted to create my own exceptions as the Oracle keywords and mapping to error numbers have been sufficient for most needs. (hence why I haven’t included an example)