PL/SQL development snippets

gistfile1.sql

/*
View: USER_SOURCE
Source of stored objects accessible to the user
---+--------+----------------+-------------------------------------------------------+
 # | column |      type      |                        remark                         |
---+--------+----------------+-------------------------------------------------------+
 1 | NAME   | VARCHAR2(30)   | Name of the object                                    |
 2 | TYPE   | VARCHAR2(12)   | Type of the object: "TYPE", "TYPE BODY", "PROCEDURE", |
   |        |                | "FUNCTION",                                           |
   |        |                | "PACKAGE", "PACKAGE BODY" or "JAVA SOURCE"            |
 3 | LINE   | NUMBER(22)     | Line number of this line of source                    |
 4 | TEXT   | VARCHAR2(4000) | Source text                                           |
---+--------+----------------+-------------------------------------------------------+

View: USER_ERRORS
Current errors on stored objects owned by the user
---+----------------+----------------+-------------------------------------------------+
 # |     column     |      type      |                           remark                |
---+----------------+----------------+-------------------------------------------------+
 1 | NAME           | VARCHAR2(30)   | Name of the object                              |
 2 | TYPE           | VARCHAR2(12)   | Type: "TYPE", "TYPE BODY", "VIEW", "PROCEDURE", |
   |                |                | "PACKAGE", "PACKAGE BODY", "TRIGGER",           |
   |                |                | "FUNCTION", "JAVA SOURCE" or "JAVA CLASS"       |
 3 | SEQUENCE       | NUMBER(22)     | Sequence number used for ordering purposes      |
 4 | LINE           | NUMBER(22)     | Line number at which this error occurs          |
 5 | POSITION       | NUMBER(22)     | Position in the line at which this error occurs |
 6 | TEXT           | VARCHAR2(4000) | Text of the error                               |
 7 | ATTRIBUTE      | VARCHAR2(9)    | [NULL]                                          |
 8 | MESSAGE_NUMBER | NUMBER(22)     | [NULL]                                          |
---+----------------+----------------+-------------------------------------------------+
*/

-- show errors in my own objects

select * from user_errors order by sequence;

-- retrieve the actual source with line numbers, which are different than the file you just loaded in (!)

select line, text
  from user_source
 where name = 'PKG_OCI_XML'
   and type = 'PACKAGE BODY';

-- source with line numbers, annotated with errors
select s.line, s.text,
       (select e.text
          from user_errors e
         where s.line = e.line 
           and s.name = e.name 
           and s.type = e.type)
       from user_source s
 where s.name = 'PKG_OCI_XML'
   and s.type = 'PACKAGE BODY';