Oracle7 Server Application Developer's Guide

Listing Information about Procedures and Packages

The following data dictionary views provide information about procedures and packages:

ALL_ERRORS, USER_ERRORS, DBA_ERRORS

ALL_SOURCE, USER_SOURCE, DBA_SOURCE

USER_OBJECT_SIZE, DBA_OBJECT_SIZE

The OBJECT_SIZE views show the sizes of the PL/SQL objects. For a complete description of these data dictionary views, see your Oracle7 Server Reference manual.

The following statements are used in Examples 1 through 3:

CREATE PROCEDURE fire_emp(emp_id NUMBER) AS
BEGIN
   DELETE FROM em WHERE empno = emp_id;
END;
/
CREATE PROCEDURE hire_emp (name VARCHAR2, job VARCHAR2,
   mgr NUMBER, hiredate DATE, sal NUMBER,
   comm NUMBER, deptno NUMBER)

IS
BEGIN
   INSERT INTO emp VALUES (emp_sequence.NEXTVAL, name,
      job, mgr, hiredate, sal, comm, deptno);
END;
/

The first CREATE PROCEDURE statement has an error in the DELETE statement. (The 'p' is absent from 'emp'.)

Example 1 Listing Compilation Errors for Objects

The following query returns all the errors for the objects in the associated schema:

SELECT name, type, line, position, text
     FROM user_errors;

The following results are returned:

NAME     TYPE LIN POS TEXT
-------- ---- --- --- -------------------------------------
FIRE_EMP PROC   3  15 PL/SQL-00201: identifier 'EM' must be
                                    declared
FIRE_EMP PROC   3   3 PL/SQL: SQL Statement ignored

Example 2 Listing Source Code for a Procedure

The following query returns the source code for the HIRE_EMP procedure created in the example statement at the beginning of this section:

SELECT line, text FROM user_source
     WHERE name = 'HIRE_EMP';

The following results are returned:

LINE   TEXT
------ -----------------------------------------------------
     1 PROCEDURE hire_emp (name VARCHAR2, job VARCHAR2,
     2    mgr NUMBER, hiredate DATE, sal NUMBER,
     3    comm NUMBER, deptno NUMBER)
     4 IS
     5 BEGIN
     6    INSERT INTO emp VALUES (emp_seq.NEXTVAL, name,
     7    job, mgr, hiredate, sal, comm, deptno);
     8 END;

Example 3 Listing Size Information for a Procedure

The following query returns information about the amount of space in the SYSTEM tablespace that is required to store the HIRE_EMP procedure:

SELECT name, source_size + parsed_size + code_size +
      error_size "TOTAL SIZE"
   FROM user_object_size
   WHERE name = 'HIRE_EMP';

The following results are returned:

NAME                           TOTAL SIZE
------------------------------ ----------
HIRE_EMP                             3897