Why cant we use SQLERRM and SQLCODE in INSERT Statement?

Yes! We all know that cannot use SQLCODE, SQLERRM in INSERT Statement! but why??

SQLCODE, SQLERRM are predefined functions which are useful in Exception handling. We can capture error code and error message from these functions in exceptions for a some piece of PL/SQL code.

Let’s see what happens if we try to use SQLCODE, SQLERRM in INSERT Statement.

Create EMP and EMP_EXCEPTIONS tables using below table creation scripts:

--Creation of EMP Table
CREATE TABLE emp
  (
    empno    NUMBER(4) NOT NULL,
    ename    VARCHAR2(10),
    job      VARCHAR2(9),
    mgr      NUMBER(4),
    hiredate DATE,
    sal      NUMBER(7, 2),
    comm     NUMBER(7, 2),
    deptno   NUMBER(2)
  );

--Creation of EMP_EXCEPTIONS table
CREATE TABLE emp_exceptions
  (
    err_code VARCHAR2(20),
    err_msg  VARCHAR2(2000)
  );

Run the below PL/SQL block of code which tries to find information of empno 1000, but here EMP table is empty since we dint insert any records, so ideally this block of code should through no data found error.

DECLARE
  name emp.ename%type;
  v_code NUMBER;
  v_errm VARCHAR2(64);
BEGIN
  SELECT ename INTO name FROM emp WHERE empno = 1000;
EXCEPTION
WHEN OTHERS THEN
  INSERT INTO emp_exceptions
    (err_code,err_msg
    ) VALUES
    (SQLCODE,sqlerrm
    );
  COMMIT;
END;
/

After running the above pl/sql code we can observer below error message since we are directly using SQLCODE and SQLERRM in INSERT statement.

Error report:
ORA-06550: line 14, column 14:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 11, column 3:
PL/SQL: SQL Statement ignored
06550. 00000 – “line %s, column %s:\n%s”
*Cause: Usually a PL/SQL compilation error.
*Action:

Why is the error coming

The purpose of above PL/SQL code is to capture the error information which occurred in a previous SQL statement into some table using SQLCODE and SQLERRM in INSERT statement. But what if the INSERT statement itself FAILS? then how will SQLCODE and SQLERRM information will be captured for both previous SQL statement and current INSERT statement? food for thought?? Yes! Ideally we can capture only one set of Error information using SQLCODE and SQLERRM, its not possible to capture both the error information that is the reason we CANNOT use SQLCODE and SQLERRM in INSERT statement.

Solution

we have to assign them to a variable and use the variables in INSERT statement as shown below.

DECLARE
  name emp.ename%type;
  v_code NUMBER;
  v_errm VARCHAR2(64);
BEGIN
  SELECT ename INTO name FROM emp WHERE empno = 1000;
EXCEPTION
WHEN OTHERS THEN
  v_code := SQLCODE;
  v_errm := SUBSTR(sqlerrm, 1 , 64);
  INSERT INTO emp_exceptions
    (err_code,err_msg
    ) VALUES
    (v_code,v_errm
    );
  COMMIT;
END;
/