Exception hanlding and logging of pl/SQL

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.


set serveroutput on

... your code

or you can redirect it to a file (see below)


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.



	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);
      DBMS_OUTPUT.PUT_LINE('No data was found - '||SQLCODE||' -ERROR- '||SQLERRM);

      DBMS_OUTPUT.PUT_LINE('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

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.



	IF input > 0 THEN
		... your pl/SQL block that does stuff...
	ELSIF input = -1
		--Raise no data found exception if 
		--Something wrong has happened. Raise an exception. 

		  DBMS_OUTPUT.PUT_LINE('Please enter a correct paramater and try again.');

		  DBMS_OUTPUT.PUT_LINE('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

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)

Tutorial 2: Creating your first Hello World eclipse project using JSP

Creating a new project in Eclipse

1. Run eclipse.exe from the installation folder.

2. Select a workspace. (if this is the first time running eclipse, just choose the default workspace which will create a folder called ‘workspace’ inside the user folder within windows.)

Select a workspace

3. Close the ‘Welcome’ page.


(You can get this page back again by selecting help-> welcome)

3. Go to file -> new -> project

New Project Wizard

4. Select Web -> Dynamic Web Project.

New Project Wizard

5. Enter a project title. For my project, I’ve named it webTest01. Click next.

 New Project Wizard
6.  This page allows you to configure the structure of your project. For now, lets leave everything as default and just click next.
New Project Wizard
7. This page allows you to make more changes to the folder structure of your project. For now, lets leave everything as default and just click finish.

New Project Wizard

8. You should now see your newly created java project as webtest01 in the project explorer to the left of the screen.

Project Explorer

Now that we have created a new project, lets create our first JSP page.(see below)

Adding a new JSP file to an existing project

1. Go to file -> new -> other. Then select web -> JSP file

Creating a new JSP file

2. Once you have created your JSP file, add the text ‘Hello World’ into your project with h1 tags.

Hello World!

3. You have now created your first JSP file. To test this, we now need to install a server and run the project. (see below)

Adding a server and running your project

Setting up a server. Before being able to run your application and access it via a browser, you will first need to install a java web server. You will be prompted to do this the first time you try and run your project, so do this now.

1. Run -> Run.

Run On Server

As we have created a web project, but don’t currently have a web server set up, the above screen should appear.

2. Choose Tomcat v7.0 Server from within the Apache folder. (you won’t be able to choose the others as they do not support the latest version of Java JDK that you have installed. To use older/other servers, you will need to download and compile your project with an older version of the JDK.) Click next.


3. Installing Tomcat.


4. As we don’t have Apache Tomcat installed, click on the download and install… button and have eclipse do this automatically for you.


5. Once complete, you should see your selected install location (in my case c:\tomcat\ in the Tomcat installation directory. Once tomcat is installed, click on the next button.

6. Make sure your project is on the configured side of the page. a5

7. Once your screen looks similar to above, click on the finish button. You will see a some red text appear as the server starts up. This is all good and is just INFO output on tomcat starting and is not typically errors.

8. Once the server has started, you can open a browser and navigate to your now running application.



Your application will always run on localhost (the keyword for your local machine) and underneath the folder of your project name. The default port your application should run on is 8080.


If your server doesn’t start or has errors:

–          Investigate if something else is currently using port 8080. In my case, I had another application using the port, which I configured to use a different port and restarted. After this my server worked correctly.

If you receive 404 page not found errors:

–          Java is case sensitive. Make sure /webTest01/ is typed in case sensitive as any variance of the case will cause the page to not load.

–         If you didn’t use index.jsp, you will need to type the full path to your jsp file.

E.g. http://localhost:8080/webtest01/myFile.jsp

Tutorial 1: Installing the JDK and an IDE

First things first, in order to do some java development, you will need to download some software. The main 2 items you will need to download and install are the Java JDK and an IDE.

The Java JDK

This is the programming libraries and underlying runtime/compiler you will need to code in java. Importantly, there are 2 different versions of java available from the download site which are the JDK and JRE. Be sure to download the JDK version, as you will not be able to actually compile your newly written java code with the JRE version

JDK = Java Developmenet Kit

JRE = Java Runtime Environment

Download link:

The download you will be looking for will be something along the lines of “Java SE 7u13 JDK”. (7u13 was the latest version at the time of writing.)

Installation steps (Windows):

  1. Download the appropriate installer for your operator system from the link above. (For the purposes of this tutorial, I will give instructions for a Windows install)
  2. Once downloaded, simply run the installer and select all of the default options. (Important note: Oracle has now started bundling the Java installers and updaters with the Ask toolbar. Under no circumstances should you install the Ask toolbar as it is essentially malware. Shame on Oracle for bundling this with java)
  3. Set your environment variables. To do this, right click on “My Computer” and click on properties
  4. Click on “Advanced System Settings”.
  5. From the System properties window, select the ‘Advanced’ tab.
  6. Select the Environment Variables button.
  7. In the “System Variables” box, scroll down to PATH and select edit.
  8. At the FRONT of all of the existing variables, insert your java installation directory followed by a semi-colan. e.g.
    C:\Program Files (x86)\Java\jdk1.7.0_10\bin;
    UPDATE: for the 64bit version of Eclipse and Windows, you will need to point the PATH variable to: C:\Program Files\Java\jdk1.7.0_13\bin and not the 32bit version.
  9. Hit OK and close all of the open windows.

Verify your installation (Windows):

  1. Press the start button and type cmd and press enter
  2. From the command prompt, type
    java -version
    If you see a console output, the JRE has installed successfully.
    C:\>java -version
    java version “1.7.0_10”
    Java(TM) SE Runtime Environment (build 1.7.0_10-b18)
    Java HotSpot(TM) Client VM (build 23.6-b04, mixed mode, sharing)
  3. From the command prompt, type
    javac -version
    if you see a console output, the JDK has installed successfully.
    C:\>javac -version
    javac 1.7.0_10



An IDE (Integrated Development Environment) is the tool you will be using to write code. While it is possible to write all of your java code in notepad, an IDE will provide a range of handy tools to assist you, such as syntax colouring and code autocomplete.

For this blog, I have chosen Eclipse as my IDE of choice as it is the one I am most familiar with.

Download link:

The download link you will be looking for will be something along the lines of “eclipse-jee-juno-SR1-win32-x86_64.zip” which was the latest x64 windows release at the time of writing.

Installation steps (Windows):

  1. Simple open the zip file and extract to contents of the zip into a new folder
    e.g. c:\eclipse\
  2. To run eclipse, simple execute c:\eclipse\eclipse.exe
  3. Create a shortcut for eclipse.exe on your desktop if needed.


Other possible IDEs include:



Oracle CPU patch update for January 2013 release

Oracle has released it’s latest CPU (Critical Patch Update) for the last quarter of 2012.

This means admins and DBAs everywhere will be currently rolling out the latest patches to all of their DEV and QA environments to see what has changed and no longer works.

Link to the Oracle official announcement:

Of particular interest is CVE-2012-3220 (see: http://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2012-3220 ) which is a vulnerability that looks to allow privilege escalation and access to the underlying OS and affects all latest versions of the Oracle Database.

The below article at threatpost.com has a lot more information on specific updates and some great discussion on particular patches: