8 Replies Latest reply: Nov 22, 2012 5:42 PM by rp0428 RSS

    Unable to use insert inside Exception block

    Manoj Gupta
      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