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)