DBE_DESCRIBE

The stored procedure DESCRIBE_PROCEDURE is used to display the parameter information of a stored procedure or function, such as the parameter name, parameter mode, and parameter location. The parameter information of a function or stored procedure is returned in a list.

The prototype of the DBE_DESCRIBE.DESCRIBE_PROCEDURE function is as follows:

DBE_DESCRIBE.DESCRIBE_PROCEDURE(
  object_name                IN  VARCHAR2,
  reserved1                  IN  VARCHAR2,
  reserved2                  IN  VARCHAR2,
  overload                   OUT DBE_DESCRIBE.NUMBER_TABLE,
  dataposition               OUT DBE_DESCRIBE.NUMBER_TABLE,
  datalevel                  OUT DBE_DESCRIBE.NUMBER_TABLE,
  argument_name              OUT DBE_DESCRIBE.VARCHAR2_TABLE,
  datatype                   OUT DBE_DESCRIBE.NUMBER_TABLE,
  default_value              OUT DBE_DESCRIBE.NUMBER_TABLE,
  in_out                     OUT DBE_DESCRIBE.NUMBER_TABLE,
  datalength                 OUT DBE_DESCRIBE.NUMBER_TABLE,
  dataprecision              OUT DBE_DESCRIBE.NUMBER_TABLE,
  scale                      OUT DBE_DESCRIBE.NUMBER_TABLE,
  radix                      OUT DBE_DESCRIBE.NUMBER_TABLE,
  spare                      OUT DBE_DESCRIBE.NUMBER_TABLE,
  include_string_constraints OUT BOOLEAN
);

• The data type of the datatype parameter is different from that of the A-compatible database. The GaussDB returns the OID of the data type, and the A-compatible database returns the number of the data type in the A-compatible database.
• The include_string_constraints parameter does not take effect on the stored procedure. The value of the include_string_constraints parameter does not change, and the return values of other parameters are not affected.
• The OIDs of the data types created by the create type operation are uncertain. Therefore, do not use these OIDs for fixed judgment.
• For the dataposition parameter, if a stored procedure is specified, the return value starts from 1. If a function is specified, the return value starts from 0. 0 indicates the position sequence number of the return value of the function.
• For the argument_name parameter, if a function is specified, the first position of the return value is empty. This position indicates the name of the return value of the described function (that is, the empty name).
• Do not directly specify a package. Otherwise, an error is reported.
• If you do not have the execute permission on a stored procedure, function, or package, the system considers that the stored procedure, function, or package does not exist and reports an error.
• The input parameters reserved1 and reserved2 are not involved in internal processing. Entering any character string does not affect the returned result.
• The advanced package cannot specify the stored procedures or functions obtained through DBLINK.
• You are advised to add the schema prefix before the specified stored procedure or function. If the schema prefix is omitted, the advanced package uses the schema of the current session to search for the entity to which the advanced package belongs. In this case, you need to change the value of behavior_compat_options to bind_procedure_searchpath for the advanced package to take effect.
• If the %type operation is used to obtain the data type from a table column, the constraint on the data type (such as NUMBER(3) and VARCHAR2(10)) is not retained.

Example:

-- Create a stored procedure to encapsulate the advanced package for printing return values.
CREATE PROCEDURE PRINT_DESCRIBE (obj_name IN VARCHAR2)
AS
  a_overload     DBE_DESCRIBE.NUMBER_TABLE;
  a_position     DBE_DESCRIBE.NUMBER_TABLE;
  a_level        DBE_DESCRIBE.NUMBER_TABLE;
  a_arg_name     DBE_DESCRIBE.VARCHAR2_TABLE;
  a_dty          DBE_DESCRIBE.NUMBER_TABLE;
  a_def_val      DBE_DESCRIBE.NUMBER_TABLE;
  a_mode         DBE_DESCRIBE.NUMBER_TABLE;
  a_length       DBE_DESCRIBE.NUMBER_TABLE;
  a_precision    DBE_DESCRIBE.NUMBER_TABLE;
  a_scale        DBE_DESCRIBE.NUMBER_TABLE;
  a_radix        DBE_DESCRIBE.NUMBER_TABLE;
  a_spare        DBE_DESCRIBE.NUMBER_TABLE;
  a_include_string_constraints BOOLEAN;
BEGIN
  DBE_DESCRIBE.DESCRIBE_PROCEDURE(
    obj_name,
    null,
    null,
    a_overload,
    a_position,
    a_level,
    a_arg_name,
    a_dty,
    a_def_val,
    a_mode,
    a_length,
    a_precision,
    a_scale,
    a_radix,
    a_spare,
    a_include_string_constraints
  );
  dbe_output.print('overload                ' || chr(9));
  for indx in 1 .. a_overload.count 
  loop 
    dbe_output.print(a_overload(indx) || chr(9)); 
  end loop;

  dbe_output.print_line(' ');

  dbe_output.print('dataposition            ' || chr(9));
  for indx in 1 .. a_position.count 
  loop 
    dbe_output.print(a_position(indx) || chr(9)); 
  end loop;

  dbe_output.print_line(' ');

  dbe_output.print('datalevel               ' || chr(9));
  for indx in 1 .. a_level.count 
  loop 
    dbe_output.print(a_level(indx) || chr(9)); 
  end loop;

  dbe_output.print_line(' ');

  dbe_output.print('argument_name           ' || chr(9));
  for indx in 1 .. a_arg_name.count 
  loop 
    dbe_output.print(a_arg_name(indx) || chr(9)); 
  end loop;

  dbe_output.print_line(' ');

  dbe_output.print('default_value           ' || chr(9));
  for indx in 1 .. a_def_val.count 
  loop 
    dbe_output.print(a_def_val(indx) || chr(9)); 
  end loop;

  dbe_output.print_line(' ');

  dbe_output.print('in_out                  ' || chr(9));
  for indx in 1 .. a_mode.count 
  loop 
    dbe_output.print(a_mode(indx) || chr(9)); 
  end loop;

  dbe_output.print_line(' ');

  dbe_output.print('length                  ' || chr(9));
  for indx in 1 .. a_length.count 
  loop 
    dbe_output.print(a_length(indx) || chr(9)); 
  end loop;

  dbe_output.print_line(' ');

  dbe_output.print('precision               ' || chr(9));
  for indx in 1 .. a_precision.count 
  loop 
    dbe_output.print(a_precision(indx) || chr(9)); 
  end loop;

  dbe_output.print_line(' ');

  dbe_output.print('scale                   ' || chr(9));
  for indx in 1 .. a_scale.count 
  loop 
    dbe_output.print(a_scale(indx) || chr(9)); 
  end loop;

  dbe_output.print_line(' ');

  dbe_output.print('radix                   ' || chr(9));
  for indx in 1 .. a_radix.count 
  loop 
    dbe_output.print(a_radix(indx) || chr(9)); 
  end loop;

  dbe_output.print_line(' ');
END;
/

-- Create a function with three overloads.
CREATE OR REPLACE FUNCTION TEST_FUNC_OVERLOAD (
  param_a IN NUMBER,
  param_b IN VARCHAR2,
  param_c OUT TEXT
)
RETURN VARCHAR2 package
AS
BEGIN
  dbe_output.print_line('This procedure/function test num param.');
  RETURN 'This procedure/function test num param.';
END;
/

CREATE OR REPLACE FUNCTION TEST_FUNC_OVERLOAD (
  param_a IN NUMBER DEFAULT 20,
  param_b VARCHAR2 DEFAULT 'n',
  param_c IN TEXT, param_d OUT DATE,
  param_e INOUT RAW
)
RETURN VARCHAR2 package
AS
BEGIN
  dbe_output.print_line('This procedure/function test num param.');
  RETURN 'This procedure/function test num param.';
END;
/

CREATE OR REPLACE FUNCTION TEST_FUNC_OVERLOAD (
  param_a IN NUMBER DEFAULT 20,
  param_b VARCHAR2 DEFAULT 'n',
  param_c IN TEXT, param_d IN DATE,
  param_e OUT RAW, param_f INOUT INTEGER
)
RETURN VARCHAR2 package
AS
BEGIN
  dbe_output.print_line('This procedure/function test num param.');
  RETURN 'This procedure/function test num param.';
END;
/

-- Call the preceding encapsulation.
BEGIN PRINT_DESCRIBE('TEST_FUNC_OVERLOAD'); END;
/

-- Clean up.
DROP FUNCTION TEST_FUNC_OVERLOAD (
  param_a IN NUMBER,
  param_b IN VARCHAR2,
  param_c OUT TEXT
);

DROP FUNCTION TEST_FUNC_OVERLOAD (param_a IN NUMBER,
  param_b VARCHAR2,
  param_c IN TEXT,
  param_d OUT DATE,
  param_e INOUT RAW
);

DROP FUNCTION TEST_FUNC_OVERLOAD (param_a IN NUMBER,
  param_b VARCHAR2,
  param_c IN TEXT,
  param_d IN DATE,
  param_e OUT RAW,
  param_f INOUT INTEGER
);

DROP PROCEDURE PRINT_DESCRIBE (obj_name IN VARCHAR2);