Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to capture Oracle errors in PL/SQL?
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;
2K+ Views
