Exception and error handling in enterprise applications

EXCEPTION
WHEN exception_name
THEN
error_handling_code
[WHEN OTHERS default_exception_handling]

Listing 8.

Like the TRY...CATCH statement in T/SQL, the exception section in PL/SQL provides an event-driven model for processing errors, because no matter what exception is raised (an application or user-based), and then the processing of the business logic is halt, and the control is transferred to the exception section. The control does not return to that part of the block once you have finished handling the error.

Before an exception is raised, it must be defined. In Oracle, you can find thousands of built-in exceptions (assigning numbers and messages). The most commonly used exceptions are found in the STANDARD package of PL/SQL.

Because many problems, that you may encounter, are specific to your solution, then you must declare your own exceptions (user-defined exceptions) in the declaration section using the following syntax (see Listing 9).

exception_name EXCEPTION;

Listing 9.

If you want to initiate an exception, you must follow the syntax (see Listing 10).

RAISE exception_name;

Listing 10.

And if you want to handle this specific exception, you must use the WHEN clause as illustrated (Listing 11).

WHEN exception_name THEN

Listing 11.

In addition, developers can raise exceptions using the RAISE_APPLICATION_ERROR built-in procedure and passing an error number (between -20000 and 20999) and an error message. The main advantage of this procedure is to associate an error message with the exception. When this procedure is invoked the changes made to parameters (IN OUT and OUT) are reversed while changes made to database objects (using SQL statement such as INSERT, UPDATE, DELETE) will not be rolled back. You must execute an explicit ROLLBACK statement.

When an exception is caught, then you can use several built-in functions to analyze the errors:

• SQLCODE. Returns the error code of the most recent exception. If there is no error, then it returns 0.
• SQLERRM. Returns the error message for a particular error code. If SQLCODE is 0, then it returns "ORA-0000: normal, successful completion". You can see an example (see Listing 12).
• DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. This function is new to Oracle 10g and returns a formatted string displaying the stack of the program and line number to the line on which the error is originated. Let's see an example (see Listing 13).

begin
dbms_output.put_line(SQLERRM(-1));
end;
/

Listing 12.

create or replace procedure proc1 is
begin
dbms_output.put_line('This is proc1');
raise no_data_found;
end;
/
create or replace procedure proc2 is
begin
dbms_output.put_line('This is proc2');
proc1;
end;
/
create or replace procedure proc3 is
begin
dbms_output.put_line('This is proc3');
proc2;
exception
when others
then
dbms_output.put_line(DBMS_UTILITY.format_error_backtrace);
end;
/
set serveroutput on;
begin
dbms_output.put_line('Testing back trace');
proc3;
end;
/

Listing 13.

Now let's rewrite the business logic in Listing 7 using the exception handling semantics in PL/SQL (see Listing 14).

create table test(x int check(x>0));
/
begin
insert into test values(1);
insert into test values(-1);
insert into test values(2);
commit tran;
exception
when others
then
dbms_output.put_line('Error on your application logic');
rollback tran;
end;

Listing 14.

Error handling in applications

Sometimes, the developer must deal with exceptions in their applications. Now we're going to show how to catch errors and warning in ADO.NET in order to be shown in our client applications.

Let's see how we can do it. When an exception is raised using RAISERROR, if the severity value is less or equal to 10, then it's generated a warning not an exception. The warnings are caught in the InfoMessage event of the Connection object. If the severity value is greater than 10, then an exception is raised and this exception is caught using the TRY...CATCH...FINALLY mechanisms of C#.

To illustrate these concepts, let's create a Windows form, and add a StatusStrip control to the form. Then inside the StatusStrip control, let's add a ToolStripStatusLabel control to display any warning sent by the database system (in our case, SQL Server) to our application. Finally, let's add a button to invoke a stored procedure in the backend server which sends warning and exceptions. The errors are displayed using a DialogBox.

First of all, let's define a stored procedure which returns a warning (see Listing 15).

create procedure spTestProc

as

begin

raiserror(N'This is a warning', 10, 1);

end;

Listing 15.

Now, let's define the C# code of client application (see Listing 16).

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

namespace ExceptionHandlingWinForms

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void statusStrip1_ItemClicked(object sender, ToolStripItemClickedEventArgs e)

{

}

private void button1_Click(object sender, EventArgs e)

{

using (SqlConnection objConnection = new SqlConnection("Data Source=localhost;Initial Catalog=TestDB;Integrated Security=True"))

{

objConnection.InfoMessage += new SqlInfoMessageEventHandler(objConnection_InfoMessage);

try

{

SqlCommand objCmd = new SqlCommand();

objCmd.Connection = objConnection;

objCmd.CommandType = CommandType.StoredProcedure;

objCmd.CommandText = "spTestProc";

objConnection.Open();

objCmd.ExecuteNonQuery();

}

catch (SqlException objEx)

{

System.Windows.Forms.MessageBox.Show(objEx.Message, "SQL Server error", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

finally

{

objConnection.Close();

}

}

}

void objConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e)

{

this.m_tsLWarning.Text = "Warning: "+e.Message;

}

}

}

Listing 16.

When you execute the application and press the button, the result is as shown (Figure 1).

Figure 1.

Let's change the code of the stored procedure in order to return an exception (see Listing 17).

alter procedure spTestProc

as

begin

raiserror(N'This is an exception', 11, 1);

end;

Listing 17.

Now, let's execute the application again and see the results (as shown in Figure 2).

Figure 2.

You can apply the same business logic to Oracle client application. Let's define a procedure in PL/SQL which returns an application error message to the application (see Listing 18).

create or replace procedure spTestProc
as
begin
raise_application_error(-20010,'Sending an application error');
end;
/

Listing 18.

When you run your application, then you will receive and handle this error using TRY...CATCH...FINALLY mechanisms.

Conclusion.