Monday, March 12, 2012

Describe PL/SQL objects

Oracle Database provides DBMS_DESCRIBE package to get information about PL/SQL object such as procedure or function. It is very simple. It takes the name of a stored PL/SQL block (procedure, packaged function etc.) and returns 12 associative arrays with information about parameters including information about low-level parameters (for example for records). Here is my simple API - DESCRIBE pacjage with PROC procedure. It provides a way to show output of DBMS_DESCRIBE.DESCRIBE_PROCEDURE in two modes. First method provides information with numeric indexes, second method explains some parts of the output as show below (table EMP from SCOTT schema was used).


CREATE OR REPLACE PROCEDURE example
(p_emp emp%ROWTYPE, p_num IN OUT NUMBER, p_str OUT VARCHAR2)
IS
BEGIN
  NULL;
END example;

SET SERVEROUTPUT ON
BEGIN
  describe.proc('EXAMPLE',TRUE);
  -- Default value for the second parameter is FALSE.
  -- It means standart output
  -- with numeric information.
END;

anonymous block completed
-- Number of columns and names are shorten for convenience.
pos  lev  argument  datatype family  dflt  in_out
---  ---  --------  ---------------  ----  ------
1    0    P_EMP     PL/SQL RECORD    0     IN
1    1    EMPNO     NUMBER           0     IN
2    1    ENAME     VARCHAR2         0     IN
3    1    JOB       VARCHAR2         0     IN
4    1    MGR       NUMBER           0     IN
5    1    HIREDATE  DATE             0     IN
6    1    SAL       NUMBER           0     IN
7    1    COMM      NUMBER           0     IN
8    1    DEPTNO    NUMBER           0     IN
2    0    P_NUM     NUMBER           0     IN OUT
3    0    P_STR     VARCHAR2         0     OUT

The source code of the DESCRIBE package is available here.

No comments:

Post a Comment