DBMS_DESCRIBE - PolarDB - Alibaba Cloud Documentation Center

In complex database development or migration scenarios, you might need to retrieve the definitions of PL/SQL functions or stored procedures at runtime. These definitions include parameter names, data types, and modes. DBMS_DESCRIBE in PolarDB for PostgreSQL (Compatible with Oracle) addresses this need, allowing you to programmatically retrieve metadata for a PL/SQL object and store it in PL/SQL collection variables. This supports dynamic code analysis, the development of debugging tools, and automated validation.

Features

DBMS_DESCRIBE is a built-in package that is highly compatible with Oracle. Its core feature is the DESCRIBE_PROCEDURE stored procedure. When you call this procedure with the name of a PL/SQL object, such as a function or stored procedure, it returns detailed descriptions of all the object's parameters.

The returned information is stored in multiple OUT parameters. These parameters are variables based on two predefined associative array types: VARCHAR2_TABLE and NUMBER_TABLE. By traversing these collections, you can access the properties of each parameter in your PL/SQL code, such as the name, position, mode (IN, OUT, or IN OUT), and data type code.

Core procedure: DBMS_DESCRIBE.DESCRIBE_PROCEDURE
Output data types:
- TYPE VARCHAR2_TABLE IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;: Stores string-type parameter properties, such as parameter names.
- TYPE NUMBER_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;: Stores numeric-type parameter properties, such as parameter position, mode, and data type code.

Benefits

Runtime code analysis: Allows you to dynamically check the interface definitions of other functions or procedures in a PL/SQL program without accessing the static data dictionary.
Improved developer efficiency: Allows you to build general-purpose tools for development, debugging, or validation and automate tasks related to procedure calls.

Applicability

Your PolarDB for PostgreSQL (Compatible with Oracle) cluster must run minor engine version 2.0.14.17.36.0 or later.

Note

You can check the minor engine version in the console or by running the SHOW polardb_version; statement. If the minor engine version requirement is not met, you can upgrade the minor engine version.

Parameters

DBMS_DESCRIBE.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
);

Parameter	Type	Description
`object_name`	VARCHAR2	Required. The name of the function or stored procedure to describe. Unlike Oracle, which converts object names to uppercase, PolarDB converts this parameter value to all lowercase for lookups. The `proc`, `package.proc`, and `schema.package.proc` formats are supported. Specifying an object using a synonym is not currently supported.
`reserved1`	VARCHAR2	Reserved parameter. Set this to `NULL` or an empty string.
`reserved2`	VARCHAR2	Reserved parameter. Set this to `NULL` or an empty string.
`overload`	NUMBER_TABLE	A unique number for the procedure that is used to distinguish between overloads. Note This parameter is not yet implemented in PolarDB. The return value has no practical meaning.
`position`	NUMBER_TABLE	The position of the parameter in the list. A value of `0` represents the return value of a function.
`level`	NUMBER_TABLE	The level of a composite type, such as a RECORD. Note This parameter is not yet implemented in PolarDB. The return value has no practical meaning.
`argument_name`	VARCHAR2_TABLE	The name of the function or stored procedure parameter.
`datatype`	NUMBER_TABLE	The data type code of the parameter. Note PolarDB returns the internal data type Object Identifier (OID), not the Oracle type code.
`default_value`	NUMBER_TABLE	Indicates whether the parameter has a default value. Note This parameter is not yet implemented in PolarDB. The return value has no practical meaning.
`in_out`	NUMBER_TABLE	The parameter mode. `0` indicates `IN`, `1` indicates `OUT`, and `2` indicates `IN OUT`.
`length`	NUMBER_TABLE	The length constraint for string types. Note This parameter is not yet implemented in PolarDB. The return value has no practical meaning.
`precision`	NUMBER_TABLE	The precision of a `NUMBER` type. Note This parameter is not yet implemented in PolarDB. The return value has no practical meaning.
`scale`	NUMBER_TABLE	The scale of a `NUMBER` type. Note This parameter is not yet implemented in PolarDB. The return value has no practical meaning.
`radix`	NUMBER_TABLE	The radix of a `NUMBER` type. Note This parameter is not yet implemented in PolarDB. The return value has no practical meaning.
`spare`	NUMBER_TABLE	Reserved field. Note This parameter is not yet implemented in PolarDB. The return value has no practical meaning.

Examples

The following examples show how to use DBMS_DESCRIBE.DESCRIBE_PROCEDURE to retrieve parameter information for different types of PL/SQL objects.

Setup

Before you run the examples, connect to your PolarDB for PostgreSQL (Compatible with Oracle) cluster. Then, create the following test stored procedures and packages.

-- 1. Create a standalone stored procedure
CREATE OR REPLACE PROCEDURE standalone_proc(
    p_varchar IN VARCHAR2,
    p_number OUT NUMBER,
    p_date IN OUT DATE
) AS
BEGIN
    NULL;
END standalone_proc;
/

-- 2. Create a package
CREATE OR REPLACE PACKAGE emp_pkg1 AS
    FUNCTION get_employee_name(p_emp_id IN NUMBER) RETURN VARCHAR2;
END emp_pkg1;
/
CREATE OR REPLACE PACKAGE BODY emp_pkg1 AS
    FUNCTION get_employee_name(p_emp_id IN NUMBER) RETURN VARCHAR2 IS
    BEGIN
        RETURN NULL;
    END get_employee_name;
END emp_pkg1;
/

-- 3. Create a schema and a package within the schema
CREATE SCHEMA emp_schema;
CREATE OR REPLACE PACKAGE emp_schema.emp_pkg2 AS
    FUNCTION get_employee_name(p_emp_id NUMBER) RETURN VARCHAR2;
END emp_pkg2;
/
CREATE OR REPLACE PACKAGE BODY emp_schema.emp_pkg2 AS
    FUNCTION get_employee_name(p_emp_id NUMBER) RETURN VARCHAR2 IS
    BEGIN
        RETURN NULL;
    END get_employee_name;
END emp_pkg2;
/

Example 1: Describe a standalone stored procedure

This example demonstrates how to obtain parameter information for standalone_proc, a standalone stored procedure that does not belong to any package.

DECLARE
    -- Declare collection variables to receive the output
    v_overload     DBMS_DESCRIBE.NUMBER_TABLE;
    v_position     DBMS_DESCRIBE.NUMBER_TABLE;
    v_level        DBMS_DESCRIBE.NUMBER_TABLE;
    v_arg_name     DBMS_DESCRIBE.VARCHAR2_TABLE;
    v_datatype     DBMS_DESCRIBE.NUMBER_TABLE;
    v_default_val  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
    -- Call the procedure to get parameter information
    DBMS_DESCRIBE.DESCRIBE_PROCEDURE(
        object_name   => 'standalone_proc',
        reserved1     => NULL,
        reserved2     => NULL,
        overload      => v_overload,
        position      => v_position,
        level         => v_level,
        argument_name => v_arg_name,
        datatype      => v_datatype,
        default_value => v_default_val,
        in_out        => v_in_out,
        length        => v_length,
        precision     => v_precision,
        scale         => v_scale,
        radix         => v_radix,
        spare         => v_spare);

    -- Format and print the results
    DBMS_OUTPUT.PUT_LINE('Position  Name         Mode');
    DBMS_OUTPUT.PUT_LINE('--------  -----------  ----');
    FOR i IN 1..v_arg_name.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(
            RPAD(v_position(i), 10) ||
            RPAD(NVL(v_arg_name(i), 'N/A'), 13) ||
            v_in_out(i)
        );
    END LOOP;
END;
/

Expected output:

Position  Name         Mode
--------  -----------  ----
1         p_varchar    0
2         p_number     1
3         p_date       2

Example 2: Describe a function within a package

This example shows how to retrieve parameter information for the get_employee_name function in the emp_pkg1 package. The package_name.procedure_name format is used.

DECLARE
    v_arg_name     DBMS_DESCRIBE.VARCHAR2_TABLE;
    v_position     DBMS_DESCRIBE.NUMBER_TABLE;
    v_in_out       DBMS_DESCRIBE.NUMBER_TABLE;
    -- Other variable declarations are omitted for brevity
    v_overload     DBMS_DESCRIBE.NUMBER_TABLE;
    v_level        DBMS_DESCRIBE.NUMBER_TABLE;
    v_datatype     DBMS_DESCRIBE.NUMBER_TABLE;
    v_default_val  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(
        object_name   => 'emp_pkg1.get_employee_name',
        reserved1     => NULL,
        reserved2     => NULL,
        overload      => v_overload,
        position      => v_position,
        level         => v_level,
        argument_name => v_arg_name,
        datatype      => v_datatype,
        default_value => v_default_val,
        in_out        => v_in_out,
        length        => v_length,
        precision     => v_precision,
        scale         => v_scale,
        radix         => v_radix,
        spare         => v_spare
    );

    DBMS_OUTPUT.PUT_LINE('Position  Name           Mode');
    DBMS_OUTPUT.PUT_LINE('--------  -------------  ----');
    FOR i IN 1..v_arg_name.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(
            RPAD(v_position(i), 10) ||
            RPAD(NVL(v_arg_name(i), 'RETURN_VALUE'), 15) || -- Position 0 is the return value
            v_in_out(i)
        );
    END LOOP;
END;
/

Expected output:

Position  Name           Mode
--------  -------------  ----
1         p_emp_id

Example 3: Describe a function in a package with a schema

This example shows how to retrieve parameter information for a function in a package under a specific schema. The schema_name.package_name.procedure_name format is used.

DECLARE
    v_arg_name     DBMS_DESCRIBE.VARCHAR2_TABLE;
    v_position     DBMS_DESCRIBE.NUMBER_TABLE;
    v_in_out       DBMS_DESCRIBE.NUMBER_TABLE;
    -- Other variable declarations are omitted for brevity
    v_overload     DBMS_DESCRIBE.NUMBER_TABLE;
    v_level        DBMS_DESCRIBE.NUMBER_TABLE;
    v_datatype     DBMS_DESCRIBE.NUMBER_TABLE;
    v_default_val  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(
        object_name   => 'emp_schema.emp_pkg2.get_employee_name',
        reserved1     => NULL,
        reserved2     => NULL,
        overload      => v_overload,
        position      => v_position,
        level         => v_level,
        argument_name => v_arg_name,
        datatype      => v_datatype,
        default_value => v_default_val,
        in_out        => v_in_out,
        length        => v_length,
        precision     => v_precision,
        scale         => v_scale,
        radix         => v_radix,
        spare         => v_spare
    );

    DBMS_OUTPUT.PUT_LINE('Position  Name           Mode');
    DBMS_OUTPUT.PUT_LINE('--------  -------------  ----');
    FOR i IN 1..v_arg_name.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(
            RPAD(v_position(i), 10) ||
            RPAD(NVL(v_arg_name(i), 'RETURN_VALUE'), 15) ||
            v_in_out(i)
        );
    END LOOP;
END;
/

Expected output:

Position  Name           Mode
--------  -------------  ----
1         p_emp_id