ORA-00984 or PLS-00231 Error when Compiling a Trigger (Doc ID 1205273.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version 10.2.0.4 and later
Information in this document applies to any platform.

Symptoms

Attempting to create a Trigger or a PL/SQL Procedure that attempts to reference SQLCODE or another built-in function can return one of the following errors.

PL/SQL: ORA-00984: column not allowed here
or
PLS-00231: function 'SQLCODE' may not be used in SQL

Here is a simple sample illustrating the error.

-- This table will be created to store the error text.

SQL> Create table Error_Table (Error_Code VARCHAR2(30));

CREATE OR REPLACE Procedure Proc_Sample1 is
BEGIN
   null;
EXCEPTION
   WHEN OTHERS THEN
      INSERT INTO Error_Table (Error_Code) VALUES (SQLCODE);
END;
/

Warning: Trigger created with compilation errors.

SQL> show error
Errors for TRIGGER SAMPLE1:

LINE/COL ERROR
-------- ---------------------------------------------------------
5/8 PL/SQL: SQL Statement ignored
5/53 PL/SQL: ORA-00984: column not allowed here



If the following Event is set within the Database, then the above error does appear but instead the following error will appear.

SQL> ALTER SESSION SET events = '10933 trace name context forever, level 512';

---Recompile the procedure

SQL> show error
Errors for TRIGGER SAMPLE1:

LINE/COL ERROR
-------- ---------------------------------------------------------------
5/8 PL/SQL: SQL Statement ignored
5/53 PLS-00231: function 'SQLCODE' may not be used in SQL




Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms