DBMS_DESCRIBE.DESCRIBE_PROCEDURE returns the parameters of a procedure or function: names, positions, data types, modes, and whether they have defaults. Code generators and generic callers use it to find out at run time how to call a subprogram.
Code for This Guide
The main examples are in the examples/pkg-utilities folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_describe.describe_procedure( object_name, reserved1 => null, reserved2 => null, overload, position, level, argument_name, datatype, default_value, in_out, length, precision, scale, radix, spare);
Every output is a PL/SQL table with one element per parameter. DATATYPE uses Oracle type codes, such as 1 for VARCHAR2 and 2 for NUMBER. IN_OUT is 0 for IN, 1 for OUT, and 2 for IN OUT; DEFAULT_VALUE is 1 when the parameter has a default.
Describe a Supplied Procedure
Example:
declare
v_overload dbms_describe.number_table;
v_position dbms_describe.number_table;
v_level dbms_describe.number_table;
v_argument dbms_describe.varchar2_table;
v_datatype dbms_describe.number_table;
v_default dbms_describe.number_table;
v_in_out dbms_describe.number_table;
v_length dbms_describe.number_table;
v_precision dbms_describe.number_table;
v_scale dbms_describe.number_table;
v_radix dbms_describe.number_table;
v_spare dbms_describe.number_table;
begin
dbms_describe.describe_procedure('DBMS_LOCK.SLEEP', null, null, v_overload, v_position,
v_level, v_argument, v_datatype, v_default, v_in_out, v_length, v_precision, v_scale,
v_radix, v_spare);
for i in 1 .. v_argument.count loop
dbms_output.put_line(v_argument(i) || ': type ' || v_datatype(i)
|| ', mode ' || v_in_out(i) || ', has default ' || v_default(i));
end loop;
end;
/Output:
SECONDS: type 2, mode 0, has default 0 PL/SQL procedure successfully completed.
DBMS_LOCK.SLEEP has one parameter, SECONDS, a NUMBER passed IN without a default.
Overloads and Return Values
FARE_CALC.PRICE is overloaded: a function and a procedure with an OUT parameter and a default.
Example:
create or replace package fare_calc is
function price (p_route number) return number;
procedure price (p_route number, p_class varchar2 default 'Y', p_total out number);
end;
/
declare
v_overload dbms_describe.number_table; v_position dbms_describe.number_table;
v_level dbms_describe.number_table; v_argument dbms_describe.varchar2_table;
v_datatype dbms_describe.number_table; v_default dbms_describe.number_table;
v_in_out dbms_describe.number_table; v_length dbms_describe.number_table;
v_precision dbms_describe.number_table; v_scale dbms_describe.number_table;
v_radix dbms_describe.number_table; v_spare dbms_describe.number_table;
begin
dbms_describe.describe_procedure('FARE_CALC.PRICE', null, null, v_overload, v_position,
v_level, v_argument, v_datatype, v_default, v_in_out, v_length, v_precision, v_scale,
v_radix, v_spare);
for i in 1 .. v_argument.count loop
dbms_output.put_line('overload ' || v_overload(i) || ', position ' || v_position(i)
|| ': ' || nvl(v_argument(i), '(return)')
|| ' type ' || v_datatype(i) || ', mode ' || v_in_out(i)
|| ', default ' || v_default(i));
end loop;
end;
/Output:
Package FARE_CALC compiled overload 1, position 0: (return) type 2, mode 1, default 0 overload 1, position 1: P_ROUTE type 2, mode 0, default 0 overload 2, position 1: P_ROUTE type 2, mode 0, default 0 overload 2, position 2: P_CLASS type 1, mode 0, default 1 overload 2, position 3: P_TOTAL type 2, mode 1, default 0 PL/SQL procedure successfully completed.
The OVERLOAD column separates the two versions. For the function, position 0 with no name is the return value. In the procedure, P_CLASS has a default and P_TOTAL is OUT. The example drops the package afterward.
Things to Know
- The name can be procedure, package.procedure, or schema.package.procedure, and synonyms are followed.
- A subprogram without parameters returns one row with an empty name.
- For stored code, the USER_ARGUMENTS view gives the same information with a query.
Related Guides
- How to Overload Subprograms in PL/SQL Packages
- How to Resolve Object Names with DBMS_UTILITY.NAME_RESOLVE
Conclusion
DBMS_DESCRIBE.DESCRIBE_PROCEDURE lists the parameters of any procedure or function, including each overload and the return value, with types, modes, and defaults. Use it, or USER_ARGUMENTS, when code must call subprograms it does not know in advance.
