How to capture Oracle errors in PL/SQL?

OracleSoftware & CodingProgramming

Problem:

You want to capture Oracle errors in PL/SQL.

Solution

All Oracle errors can be trapped with the help of the OTHERS exception handler. Let us take an example. We will be using students table to demonstrate the usage.

Let us first look at the table structure.

Example

DESC students;

Output

Name          Null     Type        
------------- -------- ------------
STUDENT_ID             NUMBER(6)   
FIRST_NAME             VARCHAR2(20)
LAST_NAME     NOT NULL VARCHAR2(25)
EMAIL         NOT NULL VARCHAR2(40)
PHONE_NUMBER           VARCHAR2(20)
JOIN_DATE     NOT NULL DATE        
CLASS_ID      NOT NULL VARCHAR2(20)
FEES                   NUMBER(8,2) 
PROFESSOR_ID           NUMBER(6)   
DEPARTMENT_ID          NUMBER(4)  

We will now write a PL/SQL block to accept the student_id as the input and display the student first name and last name.

Example

DECLARE   v_student_id   VARCHAR2(5) := '&student_id';   v_first_name    VARCHAR2(15);   v_last_name     VARCHAR2(25); BEGIN   SELECT first_name, last_name     INTO v_first_name, v_last_name     FROM students    WHERE student_id = v_student_id;   DBMS_OUTPUT.PUT_LINE (v_first_name || ', ' || v_last_name); EXCEPTION   WHEN OTHERS   THEN     DBMS_OUTPUT.PUT_LINE (' *** An error has occurred'); END;

When a value of 43210 is passed to the block, the output is.

An error has occurred

This output informs you that an error occurred at runtime, but you do not know what the error is and what caused it. Maybe there is no record in the student table corresponding to the value provided at runtime, maybe there is a data type mismatch caused by the SELECT INTO statement, or maybe the SELECT INTO statement returned more than one row. As you can see, even though this is a simple example, a number of runtime errors might potentially occur.

We cannot always identify every possible runtime error that might occur when a program is running. Therefore, it is a good practice to include the OTHERS exception handler in our script. To improve the error-handling, Oracle platform provides two built in functions, SQLCODE and SQLERRM, that can be used with the OTHERS exception handler.

The SQLCODE function returns the Oracle error number, and the SQLERRM function returns the error message. The maximum length of a message returned by the SQLERRM function is 512 bytes, which is the maximum length of an Oracle database error message.

Let us now modify the block to include SQLCODE and SQLERRM.

Example

DECLARE   v_student_id   VARCHAR2(5) := '&student_id';   v_first_name    VARCHAR2(15);   v_last_name     VARCHAR2(25);   v_err_code NUMBER;   v_err_msg  VARCHAR2(200);   BEGIN   SELECT first_name, last_name     INTO v_first_name, v_last_name     FROM students    WHERE student_id = v_student_id;   DBMS_OUTPUT.PUT_LINE (v_first_name || ', ' || v_last_name); EXCEPTION   WHEN OTHERS   THEN   --  DBMS_OUTPUT.PUT_LINE ('An error has occurred');     v_err_code := SQLCODE;     v_err_msg  := SUBSTR(SQLERRM, 1, 200);     DBMS_OUTPUT.PUT_LINE ('Error code: '||v_err_code);     DBMS_OUTPUT.PUT_LINE ('Error message: '||v_err_msg); END;

Error code: 100
Error message: ORA-01403: no data found

This version of the script includes two new variables: v_err_code and v_err_msg. In the exception-handling section of the block, the value returned by the SQLCODE function is assigned to the variable v_err_code, and the value returned by the SQLERRM function is assigned to the variable v_err_msg. Next, the error number and the error message are displayed on the screen via the DBMS_OUTPUT.PUT_LINE statements.

Data Preparation: Data used for the problem is shown below.

Example

DROP TABLE students;
COMMIT;

CREATE TABLE students
    ( student_id    NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25) CONSTRAINT  stu_last_name_nn  NOT NULL
    , email          VARCHAR2(40) CONSTRAINT  stu_email_nn  NOT NULL
    , phone_number   VARCHAR2(20)
      , join_date      DATE CONSTRAINT           stu_join_date_nn  NOT NULL
    , class_id         VARCHAR2(20) CONSTRAINT stu_job_nn  NOT NULL
    , fees           NUMBER(8,2)
    , professor_id     NUMBER(6)
    , department_id  NUMBER(4)
    , CONSTRAINT     stu_fees_min CHECK (fees > 0)
    , CONSTRAINT     stu_email UNIQUE (email)
    ) ;

Example

CREATE UNIQUE INDEX stu_id_pk ON students (student_id) ;
INSERT INTO students VALUES (100,'SMITH','JAMES','SMITH.JAMES@notreal.com','111.111.1245',TO_DATE('17-06-2003','DD-MM-YYYY'),'INS_CHAIRMAN',24000,NULL,NULL);
INSERT INTO students VALUES (101,'JOHNSON','JOHN','JOHNSON.JOHN@notreal.com','111.111.1246',TO_DATE('21-09-2005','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (102,'WILLIAMS','ROBERT','WILLIAMS.ROBERT@notreal.com','111.111.1247',TO_DATE('13-01-2001','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (103,'BROWN','MICHAEL','BROWN.MICHAEL@notreal.com','111.111.1248',TO_DATE('03-01-2006','DD-MM-YYYY'),'INS_STAFF',9000,102,60);
INSERT INTO students VALUES (104,'JONES','WILLIAM','JONES.WILLIAM@notreal.com','111.111.1249',TO_DATE('21-05-2007','DD-MM-YYYY'),'INS_STAFF',6000,103,60);
INSERT INTO students VALUES (105,'MILLER','DAVID','MILLER.DAVID@notreal.com','111.111.1250',TO_DATE('25-06-2005','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (106,'DAVIS','RICHARD','DAVIS.RICHARD@notreal.com','111.111.1251',TO_DATE('05-02-2006','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (107,'GARCIA','CHARLES','GARCIA.CHARLES@notreal.com','111.111.1252',TO_DATE('07-02-2007','DD-MM-YYYY'),'INS_STAFF',4200,103,60);
INSERT INTO students VALUES (108,'RODRIGUEZ','JOSEPH','RODRIGUEZ.JOSEPH@notreal.com','111.111.1253',TO_DATE('17-08-2002','DD-MM-YYYY'),'CL_PHY',12008,101,100);
INSERT INTO students VALUES (109,'WILSON','THOMAS','WILSON.THOMAS@notreal.com','111.111.1254',TO_DATE('16-08-2002','DD-MM-YYYY'),'CL_MATH',9000,108,100);
INSERT INTO students VALUES (110,'MARTINEZ','CHRISTOPHER','MARTINEZ.CHRISTOPHER@notreal.com','111.111.1255',TO_DATE('28-09-2005','DD-MM-YYYY'),'CL_MATH',8200,108,100);
INSERT INTO students VALUES (111,'ANDERSON','DANIEL','ANDERSON.DANIEL@notreal.com','111.111.1256',TO_DATE('30-09-2005','DD-MM-YYYY'),'CL_MATH',7700,108,100);
INSERT INTO students VALUES (112,'TAYLOR','PAUL','TAYLOR.PAUL@notreal.com','111.111.1257',TO_DATE('07-03-2006','DD-MM-YYYY'),'CL_MATH',7800,108,100);
INSERT INTO students VALUES (113,'THOMAS','MARK','THOMAS.MARK@notreal.com','111.111.1258',TO_DATE('07-12-2007','DD-MM-YYYY'),'CL_MATH',6900,108,100);

COMMIT;
raja
Published on 05-Dec-2020 06:12:50
Advertisements