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).
The source code of the DESCRIBE package is available here.
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