Oracle PL/SQL Tutorial/System Tables Data Dictionary/user errors

Get detailed error message from user_errors

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>

Query user_errors table

SQL>
SQL> COLUMN    name FORMAT a22
SQL> COLUMN    line FORMAT 9999
SQL> COLUMN    text FORMAT a30 word_wrapped
SQL> SELECT    name, type, line, text
  2  FROM      user_errors
  3  where     rownum < 2
  4  ORDER BY  name, type, sequence;
NAME                   TYPE          LINE TEXT
---------------------- ------------ ----- ------------------------------
WAIT                   PROCEDURE        3 PL/SQL: Statement ignored
SQL>
SQL>

Viewing Errors in a Database

SQL>
SQL> SELECT LINE, TYPE, NAME, TEXT from user_errors;
INE TYPE       NAME                            TEXT
1 PROCEDURE    P                    PLW-06002: Unreachable code
10 FUNCTION    DELETEEMPLOYEE       PL/SQL: ORA-00904: "EMPLOYEE": invalid identifier
7 PROCEDURE    DELETEMYEMPLOYEE     PL/SQL: ORA-00905: missing keyword
7 FUNCTION     DELETEEMPLOYEE       PL/SQL: SQL Statement ignored
4 PROCEDURE    DELETEMYEMPLOYEE     PL/SQL: SQL Statement ignored
3 PROCEDURE    HELLOFLE             PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following:
LINE TYPE         NAME           TEXT
--------------------------------------------------------------------------
   begin function package pragma procedure subtype type use
   <an identifier> <a double-quoted delimited-identifier> form
   current cursor external language
The symbol "begin" was substituted for "DECLARE" to continue.
        18 PROCEDURE    HELLOFLE
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
   begin case declare end exception exit for goto if loop mod
   null pragma raise return select update while with
   <an identifier> <a double-quoted delimited-identifier>
   <a bind variable> << close current delete fetch lock insert
   open rollback savepoint set sql execute commit forall merge
   pipe
        24 PROCEDURE    COMPILE_WARNING
      LINE TYPE         NAME
---------- ------------ ------------------------------
TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PLW-06002: Unreachable code
         1 PACKAGE      PKG
PLS-00103: Encountered the symbol "TEST1" when expecting one of the following:
   is authid as compress compiled wrapped
The symbol "authid" was substituted for "TEST1" to continue.

9 rows selected.