Exception Handing in Oracle Stored Procedure

Back to: Oracle Tutorials for Beginners and Professionals

Exception Handing in Oracle Stored Procedure

In this article, I am going to discuss Exception Handing in Oracle Stored Procedure with Examples. This article is very important. Please read our previous article where we discussed Notations of Passing Parameters in Oracle Stored Procedure with Examples.

Exception Handing in Oracle Stored Procedure

If you are directly coming to this article, then please execute the below Create Table statement to create the PRODUCTS table.

CREATE TABLE PRODUCTS
( 
  PROD_ID NUMBER,
  PROD_NAME VARCHAR2(20),
  PROD_TYPE VARCHAR2(20),
  CONSTRAINT PRODUCTS_PK PRIMARY KEY (PROD_ID)
);

Else previously we created a table called PRODUCTS. Let us go ahead and delete the rows in the table PRODUCTS and work on it freshly by executing the below delete statement.

DELETE PRODUCTS;

So, the rows in the table are deleted. Let us go ahead and check the table and see if the table is empty or not by executing the below select statement.

SELECT * FROM PRODUCTS;

So, the table is empty. Do remember that the PROD_ID column in the table PRODUCTS has a primary key. Let us go ahead and create a procedure with the name ADD_PRODUCTS where executing the procedure will add items to the PRODUCTS table. Let us see the procedure statement once.

CREATE OR REPLACE PROCEDURE ADD_PRODUCTS
(
   P_PROD_ID NUMBER, 
   P_PROD_NAME VARCHAR2:='UNKNOWN', 
   P_PROD_TYPE VARCHAR2 DEFAULT 'Unknown'
)
IS
BEGIN
  INSERT INTO PRODUCTS VALUES (P_PROD_ID, P_PROD_NAME ,P_PROD_TYPE);
  DBMS_OUTPUT.PUT_LINE (P_PROD_ID||' '||P_PROD_NAME||'  INSERTED ' );
  COMMIT;
  EXCEPTION
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE ('ERROR IN INSERT '||P_PROD_ID||' '||P_PROD_NAME);
  DBMS_OUTPUT.PUT_LINE (SQLCODE);
  DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;

From the above statement, you can see the procedure name is ADD_PRODUCTS where the parameters are P_PROD_ID, P_PROD_NAME, and P_PROD_TYPE. The parameters P_PROD_NAME and P_PROD_TYPE will have a default value i.e. “Unknown”.

This procedure will insert the IN parameters into the table PRODUCTS. After inserting the parameters this also will display DBMS_OUTPUT as (P_PROD_ID and P_PROD_NAME inserted).

In the exception case, this will show the error in the same P_PROD_ID and P_PROD_NAME. We have also opted for SQL code and SQL error messages. Let us go ahead and execute this procedure.

The procedure is now compiled successfully. Now, we will call this stored procedure from an anonymous block by executing the below statements.

BEGIN
ADD_PRODUCTS (10,'Bajaj');
ADD_PRODUCTS (10,'Laptop');
ADD_PRODUCTS (20,'Samsung');
END; 

So, we are calling the procedure three times. The first call with insert a PROD_ID as 10 and PROD_NAME as Bajaj. The second call will give us an error because PROD_ID is a primary key constraint, so we will be getting an error. The third calling will be inserted into the table. Let us go ahead and run the anonymous block.

So, the procedure is executed successfully. Let us go ahead and check the DBMS output.

From the output, you can see the Bajaj record is inserted. While inserting the second record Laptop, we are getting a unique constraint error because PROD_ID 10 is already present in the table. The third record Samsung is inserted. So, this means that the constraint error is working fine. Let us go ahead and check the table by executing the following select statement.

SELECT * FROM PRODUCTS;

So, the table products do have only two records that were inserted. When we called the procedure for the first time the procedure executed successfully because there are no records present inside the table and for the second call the table had already the same record present with PROD_ID, so we are getting the constraint error the compiler does not go to the insert statement instead it goes to exception statement and display that error in the PROD_ID and PROD_NAME. The third calling will be executed successfully.

Oracle Stored Procedure without Exception Handling:

Now, let us see another example. Let us delete the records from the table by executing the following delete statement and re-create the procedure by making a few changes.

DELETE PRODUCTS;

As we have deleted the records, let us check the table products by executing the following Select Statement.

SELECT * FROM PRODUCTS;

So, the table is empty now. Let us recreate the procedure again but this time we are not using exception handling.

CREATE OR REPLACE PROCEDURE ADD_PRODUCTS
(
   P_PROD_ID NUMBER,
   P_PROD_NAME VARCHAR2:='Unknown',
   P_PROD_TYPE VARCHAR2 DEFAULT 'Unknown'
)
IS
BEGIN
   INSERT INTO PRODUCTS VALUES (P_PROD_ID, P_PROD_NAME, P_PROD_TYPE);
   DBMS_OUTPUT.PUT_LINE (P_PROD_ID||' '||P_PROD_NAME||'  INSERTED ' );
   COMMIT;
END;

Here, you can see the procedure is the same as before but we have removed the exception part from the procedure. Let us go ahead and compile this procedure.

So, the procedure is now compiled. Now, let us try to call the procedure from an anonymous block. We will be using the same products to call this procedure.

BEGIN
ADD_PRODUCTS (10,'Bajaj');
ADD_PRODUCTS (10,'Laptop');
ADD_PRODUCTS (20,'Samsung');
END; 

If you go ahead and execute these statements, then oracle takes the first calling of the procedure then record 10 and Bajaj will be inserted into the table products. Then the other two records will not be inserted because the second statement will give us a constraint error and there is no exception handling so the third statement will not be executed. Only the first statement will be executed. Let us go ahead and execute this anonymous block.

So, we are getting the error while adding the second record. As we said, this error is because of the primary key constraint. Let us try to check the table PRODUCTS by executing the following select statement.

SELECT * FROM PRODUCTS;

Here, you can see only one record. This indicates that we need to write professional code to maintain exception handling.

Oracle Stored Procedure without Commit and without Exception Handling:

Let us check the other case. Before checking the last case, let us delete the records from the table PRODUCTS by executing the following delete statements.

DELETE PRODUCTS;

So, the rows in the table are deleted. Let us go ahead and check the table and see if the table is empty or not by executing the following select statement.

SELECT * FROM PRODUCTS;

So, the table is now empty. Let us learn about the other method of exception handling. In the previous procedure, we used commit inside the procedure. Now, let us remove the commit inside the procedure and compile the procedure again.

CREATE OR REPLACE PROCEDURE ADD_PRODUCTS
(
   P_PROD_ID NUMBER, 
   P_PROD_NAME VARCHAR2:='Unknown', 
   P_PROD_TYPE VARCHAR2 DEFAULT 'Unknown'
)
IS
BEGIN
    INSERT INTO PRODUCTS VALUES (P_PROD_ID, P_PROD_NAME, P_PROD_TYPE);
    DBMS_OUTPUT.PUT_LINE (P_PROD_ID||' '||P_PROD_NAME||' INSERTED ' );
END;

This is the same condition and the same parameters. We have just removed the commit in this procedure. Let us go ahead and execute this procedure.

So, the procedure is now compiled successfully. Let us now try to test this procedure by inserting the same record again.

BEGIN
ADD_PRODUCTS (10,'Bajaj');
ADD_PRODUCTS (10,'Laptop');
ADD_PRODUCTS (20,'Samsung');
COMMIT;
END; 

But before executing this procedure let us try to analyze these statements. The first procedure called ADD_PRODUCTS (10, ‘Bajaj’) will be executed successfully. The second procedure call will give us an error which will end up in exiting outside of the anonymous block. So, this block will not execute the third procedure called ADD_PRODUCTS (20, ‘Samsung’) and commit. So, everything that has been inserted into the table will be rollbacked. Let us try to run this block and see if it works exactly or not.

So, we got an error showing a unique constraint error which we got earlier. Let us try to check the table again by executing the below select statement.

SELECT * FROM PRODUCTS;

So, as we discussed earlier that everything will be rollbacked. So, there is no data present inside the table PRODUCTS. This is how exception and commit work in the oracle stored procedure. In most cases, we need to use the exception to avoid errors and display the errors that occurred while calling the procedure.

In the next article, I am going to discuss How to Create Boolean Parameters in Oracle Stored Procedure with Examples. Here, in this article, I try to explain Exception Handing in Oracle Stored Procedure with Examples. I hope you enjoy this Exception Handing in Oracle Stored Procedure article.