Oracle PL/SQL/System Tables Views/USER ERRORS

Data dictionary views for valid and invalid

   <source lang="sql">
   

SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE Simple AS

 2    v_Counter NUMBER;
 3  BEGIN
 4    v_Counter := 7;
 5  END Simple;
 6  /

Procedure created. SQL> SQL> COLUMN object_name format a20 SQL> COLUMN line format 9999 SQL> COLUMN position format 99999 SQL> COLUMN text format a59 SQL> SQL> SQL> SQL> SELECT object_name, object_type, status

 2    FROM user_objects
 3    WHERE object_name = "SIMPLE";

OBJECT_NAME OBJECT_TYPE STATUS

---

------------------- ----------

SIMPLE PROCEDURE VALID 1 row selected. SQL> SQL> SELECT text FROM user_source WHERE name = "SIMPLE" ORDER BY line; TEXT

---

PROCEDURE Simple AS

 v_Counter NUMBER;

BEGIN

 v_Counter := 7;

END Simple; 5 rows selected. SQL> SQL> SELECT line, position, text FROM user_errors WHERE name = "SIMPLE" ORDER BY sequence; no rows selected SQL> SQL> CREATE OR REPLACE PROCEDURE Simple AS

 2    v_Counter NUMBER;
 3  BEGIN
 4    v_Counter := 7
 5  END Simple;
 6  /

Warning: Procedure created with compilation errors. SQL> SQL> SQL> SQL> SELECT object_name, object_type, status FROM user_objects WHERE object_name = "SIMPLE"; OBJECT_NAME OBJECT_TYPE STATUS

---

------------------- ----------

SIMPLE PROCEDURE INVALID 1 row selected. SQL>

 </source>
   
  

Get detailed error message from user_errors

   <source lang="sql">
   

SQL> SQL> CREATE OR REPLACE PROCEDURE Simple AS

 2    v_Counter NUMBER;
 3  BEGIN
 4    v_Counter := 7;
 5  END Simple;
 6  /

Procedure created. SQL> SQL> COLUMN object_name format a20 SQL> COLUMN line format 9999 SQL> COLUMN position format 99999 SQL> COLUMN text format a59 SQL> SQL> SELECT object_name, object_type, status

 2    FROM user_objects
 3    WHERE object_name = "SIMPLE";

OBJECT_NAME OBJECT_TYPE STATUS

---

------------------- ----------

SIMPLE PROCEDURE VALID 1 row selected. SQL> SQL> SELECT text FROM user_source WHERE name = "SIMPLE" ORDER BY line; TEXT

---

PROCEDURE Simple AS

 v_Counter NUMBER;

BEGIN

 v_Counter := 7;

END Simple; 5 rows selected. SQL> SQL> SELECT line, position, text FROM user_errors WHERE name = "SIMPLE" ORDER BY sequence; no rows selected SQL> SQL> CREATE OR REPLACE PROCEDURE Simple AS

 2    v_Counter NUMBER;
 3  BEGIN
 4    v_Counter := 7
 5  END Simple;
 6  /

Warning: Procedure created with compilation errors. SQL> SQL> SQL> SELECT line, position, text FROM user_errors WHERE name = "SIMPLE" ORDER BY sequence;

LINE POSITION TEXT

---

-------- -----------------------------------------------------------

   5        1 PLS-00103: Encountered the symbol "END" when expecting one
              of the following:
                 * & = - + ; < / > at in is mod remainder not rem
                 <an exponent (**)> <> or != or ~= >= <= <> and or like L
              IKE2_
                 LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
              The symbol ";" was substituted for "END" to continue.

1 row selected. SQL>

 </source>
   
  

Viewing errors in the database

   <source lang="sql">
 

SQL> SQL> SQL> -- Viewing errors in the database. SQL> SELECT LINE, TYPE, NAME, TEXT from user_errors where rownum < 10;

     LINE TYPE         NAME

---

------------ ------------------------------

TEXT

---

        3 FUNCTION     EXITFUNC

PLS-00103: Encountered the symbol "=" when expecting one of the following:

  constant exception <an identifier>
  <a double-quoted delimited-identifier> table LONG_ double ref
  char time timestamp interval date binary national character
  nchar

The symbol "<an identifier>" was substituted for "=" to continue.

SQL> SQL> SQL>

 </source>