How to Describe Procedures with DBMS_DESCRIBE

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.