PL/SQL Exceptions

An exception is a PL/SQL error that is raised during program execution. PL/SQL supports programmers to catch errors using Exception block in the program and appropriate action is taken against the error condition.

Exception types

There are three types of exceptions:

  • Predefined exceptions are error conditions that are defined by PL/SQL.
  • Non-predefined exceptions include any standard TimesTen errors.
  • User-defined exceptions are exceptions specific to your application.

Syntax for Exception Handling.

DECLARE 
  
BEGIN 
  
EXCEPTION 
  
 WHEN exception1 THEN 
 exception1-handling-statements 
 WHEN exception2 THEN 
 exception2-handling-statements 
 WHEN exception3 THEN 
 exception3-handling-statements 
 ........ 
 WHEN others THEN 
 others-handling-statements 
END;

Example 1:

DECLARE
   n_empno emp.empno%type := &empno;
   v_ename emp.ename%type;
   v_job emp.job%type;
BEGIN
   SELECT ename,job into v_ename,v_job
   from emp where empno=n_empno;
   DBMS_OUTPUT.PUT_LINE('The emp details are' || v_ename || v_job);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
   DBMS_OUTPUT.PUT_LINE('The empno is not available');
END;
/

Example 2:

DECLARE
    n_accno accounts.accno%type:=&accno;
    v_name accounts.name%type:='&name';
    v_bal accounts.bal%type:=&bal;
BEGIN
    INSERT INTO accounts(accno,name,bal)
    VALUES(v_accno,v_name,v_bal);
    DBMS_OUTPUT.PUT_LINE('Account details inserted successfully');
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
    DBMS_OUTPUT.PUT_LINE('Account aleady exists');
END;
/

Example 3:

DECLARE
    n_empno emp.empno%type;
    v_ename emp.ename%type;
    n_deptno emp.deptno%type;
BEGIN
    SELECT empno,ename,deptno
    INTO n_empno,v_ename,n_deptno
    FROM emp WHERE empno=115 and ename='John';
    DBMS_OUTPUT.PUT_LINE(v_name || 'Works in ' || n_deptno || 'department');
    SELECT empno,ename,deptno
    INTO n_empno,v_ename,n_deptno
    FROM emp WHERE deptno=20;
    DBMS_OUTPUT.PUT_LINE('Employee no is' || n_empno);
    DBMS_OUTPUT.PUT_LINE('Employee name is' || v_ename);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No data found');
    WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('More than one employee works in dept no 20');
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occured while processing the program');
END;
/

Predefined Exceptions :

Predefined exceptions are internally defined exceptions that have predefined names, which PL/SQL declares globally in the package STANDARD. The runtime system raises predefined exceptions implicitly (automatically). Because predefined exceptions have names, you can write exception handlers specifically for them.

lists the names and error codes of the predefined exceptions.

Exception Name Error Code
ACCESS_INTO_NULL -6530
CASE_NOT_FOUND -6592
COLLECTION_IS_NULL -6531
CURSOR_ALREADY_OPEN -6511
DUP_VAL_ON_INDEX -1
INVALID_CURSOR -1001
INVALID_NUMBER -1722
LOGIN_DENIED -1017
NO_DATA_FOUND +100
NO_DATA_NEEDED -6548
NOT_LOGGED_ON -1012
PROGRAM_ERROR -6501
ROWTYPE_MISMATCH -6504
SELF_IS_NULL -30625
STORAGE_ERROR -6500
SUBSCRIPT_BEYOND_COUNT -6533
SUBSCRIPT_OUTSIDE_LIMIT -6532
SYS_INVALID_ROWID -1410
TIMEOUT_ON_RESOURCE -51
TOO_MANY_ROWS -1422
VALUE_ERROR -6502
ZERO_DIVIDE -1476

Example 4:

DECLARE
 stock_price NUMBER := 9.73;
 net_earnings NUMBER := 0;
 pe_ratio NUMBER;
BEGIN
 pe_ratio := stock_price / net_earnings; -- raises ZERO_DIVIDE exception
 DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio);
EXCEPTION
 WHEN ZERO_DIVIDE THEN
 DBMS_OUTPUT.PUT_LINE('Company had zero earnings.');
 pe_ratio := NULL;
END;
/

User-Defined Exceptions

A user defined exception is an error that is defined by the program. The developer to handle the business situations define use-defined exceptions during the execution of the PL/SQL block.

An exception name declaration has this syntax:

exception_name EXCEPTION;

Steps

1. Declare exception
2. Raise in executable section explicitly using RAISE ;
3. Handle the raised exception.

Raising Exceptions

Syntax :

DECLARE
exception_name EXCEPTION; 
BEGIN
IF condition THEN 
 RAISE exception_name; 
 END IF;
EXCEPTION
WHEN exception_name THEN 
 statement;
END;
/

Example 5:

DECLARE 
 c_id customers.id%type := &cc_id; 
 c_name customers.name%type; 
 c_addr customers.address%type; 
 -- user defined exception 
 ex_invalid_id EXCEPTION; 
BEGIN
IF c_id <= 0 THEN 
 RAISE ex_invalid_id; 
 ELSE 
 SELECT name, address INTO c_name, c_addr 
 FROM customers 
 WHERE id = c_id;
 DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name); 
 DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr); 
 END IF;

EXCEPTION 
 WHEN ex_invalid_id THEN 
 dbms_output.put_line('ID must be greater than zero!'); 
 WHEN no_data_found THEN 
 dbms_output.put_line('No such customer!'); 
 WHEN others THEN 
 dbms_output.put_line('Error!'); 
END; 
/

RAISE_APPLICATION_ERROR

This built-in procedure is used to create your own error message, which can be more descriptive than named Exceptions.
It is used to communicate a predefined exception interactively by returning a non standard error code and error message.
Using this procedure we can report error to application and avoid returning unhandled exception.

Syntax :

RAISE_APPLICATION_ERROR (error_number,'error_message');

Note :

  • Error number must exists between -20000 and -20999
  • Error_message is the text associate with this error, and keep_errors is Boolean value.
  • The error_message parameter must less than 512 characters.

Example 6:

DECLARE
 i emp%rowtype;
BEGIN
 i.empno:=&eno;
 SELECT ename,sal INTO i.ename,i.sal
 FROM emp WHERE empno=i.empno;
 IF i.sal < 2000 THEN
 RAISE_APPLICATION_ERROR(-20345,'The emp sal is less than 2000');
 
 ELSE
 i.sal := i.sal+i.sal*0.35;
 UPDATE emp set sal=i.sal
 WHERE empno=i.empno;
 dbms_output.put_line('Salary updated to' || i.sal);

END IF;
END;
/

Non-Predefined exceptions

We can associate an exception with an error code. The EXCEPTION_INIT pragma associates a user-defined exception name with an error code. The EXCEPTION_INIT pragma can appear only in the same declarative part as its associated exception, anywhere after the exception declaration.

Syntax :

PRAGMA EXCEPTION_INIT (exception_name, error_code);
DECLARE
 exception_name EXCEPTION;
 PRAGMA EXCEPTION_INIT(exception_name, -60);
BEGIN
 ...
EXCEPTION
 WHEN exception_name THEN
 ...
END;
/

Example 7:

CREATE PROCEDURE account_status (
 due_date DATE,
 today DATE
) AUTHID DEFINER
IS
BEGIN
 IF due_date < today THEN -- explicitly raise exception
 RAISE_APPLICATION_ERROR(-20000, 'Account past due.');
 END IF;
END;
/
 
DECLARE
 past_due EXCEPTION; -- declare exception
 PRAGMA EXCEPTION_INIT (past_due, -20000); -- assign error code to exception
BEGIN
 account_status ('1-JUL-10', '9-JUL-10'); -- invoke procedure
EXCEPTION
 WHEN past_due THEN -- handle exception
 DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLERRM(-20000)));
END;
/

SQLCODE Function

In an exception handler, the SQLCODE function returns the numeric code of the exception being handled. (Outside an exception handler, SQLCODE returns 0.)
For an internally defined exception, the numeric code is the number of the associated Oracle Database error. This number is negative except for the error “no data found”, whose numeric code is +100.

For a user-defined exception, the numeric code is either +1 (default) or the error code associated with the exception by the EXCEPTION_INIT pragma.

A SQL statement cannot invoke SQLCODE.

SQLERRM

The SQLERRM function returns the error message associated with an error code.

Like SQLCODE, SQLERRM without error_code is useful only in an exception handler. Outside an exception handler, or if the value of error_code is zero, SQLERRM returns ORA-0000.

If the value of error_code is +100, SQLERRM returns ORA-01403.

If the value of error_code is a positive number other than +100, SQLERRM returns this message:

-error_code: non-ORACLE exception

Ex:

BEGIN
 DBMS_OUTPUT.PUT_LINE('SQLERRM(-6511): ' || TO_CHAR(SQLERRM(-6511)));
END;
/

SQLERRM(-6511): ORA-06511: PL/SQL: cursor already open

Example 8:

CREATE TABLE errors (
 code NUMBER,
 message VARCHAR2(64)
);

CREATE OR REPLACE PROCEDURE p AUTHID DEFINER AS
 name EMPLOYEES.LAST_NAME%TYPE;
 v_code NUMBER;
 v_errm VARCHAR2(64);
BEGIN
 SELECT last_name INTO name
 FROM EMPLOYEES
 WHERE EMPLOYEE_ID = -1;
EXCEPTION
 WHEN OTHERS THEN
 v_code := SQLCODE;
 v_errm := SUBSTR(SQLERRM, 1, 64);
 DBMS_OUTPUT.PUT_LINE
 ('Error code ' || v_code || ': ' || v_errm);
 
 /* Invoke another procedure,
 declared with PRAGMA AUTONOMOUS_TRANSACTION,
 to insert information about errors. */
 
 INSERT INTO errors (code, message)
 VALUES (v_code, v_errm);

RAISE;
END;
/

Error code 100: ORA-01403: no data found

 

Advertisements

Post your Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s