This discussion is archived
8 Replies Latest reply: Nov 22, 2012 3:42 PM by rp0428 RSS

Unable to use insert inside Exception block

Manoj Gupta Newbie
Currently Being Moderated
Hi,

Below plsql code tries to insert null value to Department_Name column of Departments table in HR schema. The operation will not be successful because Department_name is not null column. The code is further trapping the Oracle server error number -01400(cannot insert null) by creating a handler e_insert using PRAGMA psuedoinstructions.

Errors Table Definition:
CREATE TABLE ERRORS
  (
    E_USER NUMBER,
    E_DATE DATE,
    ERROR_CODE    NUMBER,
    ERROR_MESSAGE VARCHAR2(255)
  );
pl/sql:*
DECLARE
  ERR_NUM NUMBER;
  ERR_MSG VARCHAR2(255);  
  
  E_INSERT EXCEPTION;
  PRAGMA EXCEPTION_INIT(E_INSERT, -01400);
  
BEGIN
  INSERT INTO DEPARTMENTS
    (DEPARTMENT_ID, DEPARTMENT_NAME) VALUES
    (280, NULL);
EXCEPTION
WHEN E_INSERT THEN
  ERR_NUM := SQLCODE;
  ERR_MSG := SUBSTR(SQLERRM,1,100);  
  dbms_output.put_line('Error Code '||ERR_NUM||': '||ERR_MSG);
  INSERT INTO ERRORS(E_USER,E_DATE,ERROR_CODE,ERROR_MESSAGE)
  VALUES(USER,SYSDATE,ERR_NUM,ERR_MSG);  
END;
/
OUTPUT:_

Error Code -1400: ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_NAME")
DECLARE
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 15
ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_NAME")



No issues after removing insert from exception block:_
DECLARE
  ERR_NUM NUMBER;
  ERR_MSG VARCHAR2(255);  
  
  E_INSERT EXCEPTION;
  PRAGMA EXCEPTION_INIT(E_INSERT, -01400);
  
BEGIN
  INSERT INTO DEPARTMENTS
    (DEPARTMENT_ID, DEPARTMENT_NAME) VALUES
    (280, NULL);
EXCEPTION
WHEN E_INSERT THEN
  ERR_NUM := SQLCODE;
  ERR_MSG := SUBSTR(SQLERRM,1,100);  
  dbms_output.put_line('Error Code '||ERR_NUM||': '||ERR_MSG);
END;
/
OUTPUT:_

Error Code -1400: ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_NAME")

-----

Kindly advise why insert statement is causing the exception.

Thank you
Manoj