DBMS_DESCRIBE PACKAGE를 이용하여 PROCEDURE, FUNCTION의 ARGUMENT를 확인하는 방법
===============================================================================


Purpose
-------
DBMS_DESCRIBE package를 이용하면 특정 procedure, function의 argument를
확인할 수 있다. (대상이 되는 procedure, function이 standalone이거나
package내부에 존재하거나 모두 가능하다.)


Explanation
-----------

DBMS_PACKAGE를 사용하려면 우선,
$ORACLE_HOME/rdbms/admin directory의 dbmsdesc.sql, prvtdesc.sql을
sys user로 수행해야 한다.(catproc.sql의 내부에서 수행되도록 되어 있다.)

이 package안에는 DESCRIBE_PROCEDURE라는 하나의 procedure만이 존재한다.

procedure describe_procedure (
object_name in varchar2,
reserved1 in varchar2,
reserved2 in varchar2,
overload out number_table,
position out number_table,
level out number_table,
argument_name out varchar2_table,
datatype out number_table,
default_value out number_table,
in_out out number_table,
length out number_table,
precision out number_table,
scale out number_table,
radix out number_table,
spare out number_table ) ;

이 procedure는 특정 procedure, function의 이름을 받아서 해당 procedure,
function의 argument에 대한 내용을 return한다.
return되는 argument에 대한 내용들은 모두 indexed PL/SQL table의 형태이다.
이들은 overload, position에 대해서 ordered된다.

이 package를 사용하는데 있어서 다음과 같은 제약사항이 있다.
. remote object에 대한 description은 추출할 수 없다.
. 특정 package내의 모든 procedure, function들에 대한 description을
한번에 return하지는 못한다.

input 변수로 받는 procedure, function의 이름(object_name)은 SQL의
규칙을 따른다. 만약 procedure, function이 특정 package내에 존재한다면
'object_name' argument는 package_name.procedure_name의 형태로 사용한다.
만약 DBMS_DESCRIBE package를 사용하는 user가, 대상이 되는 procedure,
function의 owner가 아니라면 'object_name' argument는
owner_name.obj_name형태를 사용해야 하며(이때 obj_name은 procedure_name,
function_name, package_name.procedure_name, package_name.function_name
중에 한가지이다.), EXECUTE privilege를 가지고 있어야 한다.


Example
-------

1. 예제1
이후에 진행되는 모든 example에서는 아래에 정의된 MYPACK package의 FUNC1
function을 이용한다.

create or replace package mypack as
type numtabtype is table of number(9) index by binary_integer;
type chartabtype is table of char(10) index by binary_integer;
type datetabtype is table of date index by binary_integer;
function func1 (tab_size in number, tabl in numtabtype) return chartabtype;
function func1 (tab_size in number, tabl in datetabtype) return chartabtype;
end;
/

create or replace package body mypack as
function func1 (tab_size in number, tabl in numtabtype)
return chartabtype
is
temptab chartabtype;
begin
for i in 1..tab_size loop
temptab(i) := to_char(tabl(i));
end loop;
return temptab;
end;

function func1 (tab_size in number, tabl in datetabtype)
return chartabtype
is
temptab chartabtype;
begin
for i in 1..tab_size loop
temptab(i) := to_char(tabl(i));
end loop;
return temptab;
end;
end;
/


2. PL/SQL을 이용한 예

stored procedure내에서 DBMS_DESCRIBE.DESCRIBE_PROCEDURE를 call하는 방법

create or replace procedure describe_any (obj_name in varchar2) as
overload dbms_describe.number_table;
position dbms_describe.number_table;
levl dbms_describe.number_table;
argument_name dbms_describe.varchar2_table;
datatype dbms_describe.number_table;
default_value dbms_describe.number_table;
in_out dbms_describe.number_table;
length dbms_describe.number_table;
precision dbms_describe.number_table;
scale dbms_describe.number_table;
radix dbms_describe.number_table;
spare dbms_describe.number_table;
maxrows integer := 15;
begin
dbms_describe.describe_procedure (
obj_name,
null,
null,
overload,
position,
levl,
argument_name,
datatype,
default_value,
in_out,
length,
precision,
scale,
radix,
spare ) ;

dbms_output.put_line('overload ' ||
'position ' ||
'argument ' ||
'level ' ||
'datatype ' ||
'length ' ||
'prec ' ||
'scale ' ||
'rad ');
dbms_output.put_line('-------------------------' ||
'-------------------------' ||
'-------------------------');
for counter in 1..maxrows loop
begin
dbms_output.put( overload(counter) || ' ' ||
position(counter) || ' ');
if argument_name(counter) is null then
dbms_output.put(' ');
else
dbms_output.put(rpad(argument_name(counter),10) || ' ');
end if;
dbms_output.put_line(
levl(counter) || ' ' ||
lpad(to_char(datatype(counter)),3) || ' ' ||
lpad(to_char(length(counter)),3) || ' ' ||
precision(counter) || ' ' ||
scale(counter) || ' ' ||
lpad(to_char(radix(counter)),2) );
exception
when no_data_found then exit;
end;
end loop;
end;
/

SQL> execute describe_any('MYPACK.FUNC1');
overload position argument level datatype length prec scale rad
---------------------------------------------------------------------
1 0 0 251 0 0 0 0
1 0 1 96 10 0 0 0
1 1 TAB_SIZE 0 2 0 0 0 0
1 2 TABL 0 251 0 0 0 0
1 2 1 2 22 9 0 10
2 0 0 251 0 0 0 0
2 0 1 96 10 0 0 0
2 1 TAB_SIZE 0 2 0 0 0 0
2 2 TABL 0 251 0 0 0 0
2 2 1 12 0 0 0 0

PL/SQL procedure successfully completed.

위의 예에서 각 column의 의미는 다음과 같다.
overload : overload된(이름은 같지만 argument의 갯수, type등이 다른)
procedure나 function이 있다면 각각에 고유한 number가 부여
position : parameter list안에서의 위치
이값이 0이면 function의 return value를 의미함.
argument : argument 이름
level : 만약 argument가 composite type인 경우 각 argument의 level
datatype : argument의 datatype을 나타내는 number
0 - This row is a placeholder for a procedure with
no arguments.
1 - VARCHAR2
2 - NUMBER
3 - NATIVE INTEGER (for PL/SQL's BINARY_INTEGER)
8 - LONG
11 - ROWID
12 - DATE
23 - RAW
24 - LONG RAW
96 - CHAR (ANSI FIXED CHAR)
106 - MLSLABEL
250 - PL/SQL RECORD (see "Notes:" below)
251 - PL/SQL TABLE
252 - PL/SQL BOOLEAN (see "Notes:" below)
length : datatype의 length
prec, scale, rad : datatype이 number일 경우 사용


3. Precompiler를 이용한 예

#include
#include


/* Define constants for VARCHAR lengths. */
#define UNAME_LEN 20
#define PWD_LEN 40
#define ARRAY_SIZE 10

typedef char str[11];

EXEC SQL TYPE str IS STRING(11) REFERENCE;

/* Declare variables. No declare section is
needed if MODE=ORACLE. */
VARCHAR username[UNAME_LEN]; /* VARCHAR is an
Oracle-supplied struct */
varchar password[PWD_LEN]; /* varchar can be in
lower case also. */
varchar object_name[30];
int overload[ARRAY_SIZE];
int position[ARRAY_SIZE];
int level[ARRAY_SIZE];
str argument_name[ARRAY_SIZE];
short arg_name_ind[ARRAY_SIZE];
int datatype[ARRAY_SIZE];
int default_value[ARRAY_SIZE];
int in_out[ARRAY_SIZE];
int length[ARRAY_SIZE];
int precision[ARRAY_SIZE];
int scale[ARRAY_SIZE];
int radix[ARRAY_SIZE];
int spare[ARRAY_SIZE];

int i;

/* Include the SQL Communications Area.
You can use #include or EXEC SQL INCLUDE. */
#include


/* Declare error handling function. */
void sql_error();


main()
{

/* Connect to ORACLE--
* Copy the username into the VARCHAR.
*/
strncpy((char *) username.arr, "SCOTT", UNAME_LEN);

/* Set the length component of the VARCHAR. */
username.len = strlen((char *) username.arr);

/* Copy the password. */
strncpy((char *) password.arr, "TIGER", PWD_LEN);
password.len = strlen((char *) password.arr);

/* Register sql_error() as the error handler. */
EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\
");

/* Connect to ORACLE. Program will call sql_error()
* if an error occurs when connecting to the default database.
*/
EXEC SQL CONNECT :username IDENTIFIED BY :password;

printf("\
Connected to ORACLE as user: %s\
", username.arr);

printf("\
Enter the name of the procedure or function to be described: ");
gets((char *) object_name.arr);
object_name.len = strlen((char *) object_name.arr);

for (i=0; ifc]);
do_exit(n);
}


/*
* Exit program with an exit code.
*/
do_exit(exit_code)
sword exit_code;
{
sword error = 0;

if (ologof(&lda))
{
fprintf(stderr, "Error on disconnect.\
");
fprintf(stderr, "Error on disconnect.\
");
error++;
}

exit(exit_code);
}


결과 :

Connected to ORACLE as SCOTT
Overload Level Pos ProcName Datatype
1 0 0 251
1 1 0 96
1 0 1 TAB_SIZE 2
1 0 2 TABL 251
1 1 2 2
2 0 0 251
2 1 0 96
2 0 1 TAB_SIZE 2
2 0 2 TABL 251
2 1 2 12


Reference Document
------------------
Oracle Developer's Guide