select message_id, severity, is_event_logged, text
from sys.messages

Listing 1.

It's possible to add new user-defined message error to the former table using the sp_addmessage stored procedures. Let's define a message error indicating a prohibition to delete a table (see Listing 2).

use master;

go

exec sp_addmessage 100001,14,N'You need more privelege to delete the table %s';
go

Listing 2.

The first parameter indicates the message identifier whose value is between 50001 and 2147483647. The second parameter indicates the severity level, and the last parameter is the actual message text.

Now, let's supposed that we have detected that a user is trying to delete a very important table of our application, and then we send an error message using the command RAISERROR (see Listing 3).

raiserror(100001, 14, 1, N'admin_schema.important_table');

go

Listing 3.

The RAISERROR command is often used to return error message to business logic components of the enterprise application, for example, when the integrity of some entities is violated.

You can also raise an error message without defining the message (see Listing 4).

raiserror('You need more privelege to delete the table %s', 14, 1, N'admin_schema.important_table');

Listing 4.

Up to this point, we've seen how to define error message. Now, let's see how to handle the errors.

Prior to SQL Server 2005, error handling was done by checking the @@ERROR global variable after each statement was executed, and then using a GOTO statement (the worst of the practices) the logic was interrupted in order to go a centralized error-handling block of code (see Listing 5).

create table test(x int check(x>0));

go

begin tran

insert into test values(1);

if @@ERROR <> 0 goto Error_Handling_Code;

insert into test values(-1);

if @@ERROR <> 0 goto Error_Handling_Code;

insert into test values(2);

if @@ERROR <> 0 goto Error_Handling_Code;

commit tran;

Error_Handling_Code:

print 'Error occurred.';

rollback tran;

go

Listing 5.

Now, the TRY...CATCH statement can be used to capture execution errors in T-SQL code. The syntax is as follows (see Listing 6).

begin try
  {sql_statement | statement_block}
end try
begin catch
  {sql_statement | statement_block}
end catch

Listing 6.

If an error is encountered within the try block, then we go directly to the catch block without processing other statements in the try block. It's remarkable to notice that warning and informational messages (severity level less than 10) are not captured by the TRY...CATCH statement. You can also nest a TRY...CATCH statement inside other TRY...CATCH statement.

Let's rewrite the code in Listing 5 using the TRY...CATCH statement (see Listing 7).

create table test(x int check(x>0));

begin try

  begin tran;

  insert into test values(1);

  insert into test values(-1);

  insert into test values(2);

  commit tran;

end try

begin catch

  print 'Error ocurred. Message '+ERROR_MESSAGE();

  rollback tran;

end catch

Listing 7.

It's remarkable to say that SQL Server by default does not rollback the changes to database objects once an exception occurs. It's your decision as developer to explicitly rollback the changes. Although if you set the XACT_ABORT option to ON, then transactions are rolled back in the event of any runtime error.

Exception and error handling in Oracle database

In Oracle, we can find a good mechanism to work with exceptions. There are two types of exceptions:

• System exception. This exception is defined by Oracle and raised by the PL/SQL engine when an error is detected.
• Programmer-defined exception. An exception specific to the application and raised for some events of the application.

In PL/SQL, we can catch and handle the exceptions using the exception section (which is optional) associated to PL/SQL blocks. This section is used to handle any error that occurs during the execution of PL/SQL code inside the block. It's a way to cleanly separate the error processing logic from your business logic. The syntax for the exception section is as follows (see Listing 8). The section can have as many WHEN statements as you like to handle specific exceptions; all other exceptions are handled by the WHEN OTHERS section.

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.