How to work with DBMS_DESCRIBE.DESCRIBE_PROCEDURE

[jfudickar]

Hi,

is there an example how to work with the DBMS_DESCRIBE.DESCRIBE_PROCEDURE procedure?

The parameter results are lists of strings. How can i get these from the plsql back into my delphi code?

Greetings and thanks
Jens

[Plash]

ODAC does not support PL/SQL table parameters. Therefore you should use the TOraSQL or TOraQuery component with PL/SQL code that will call the DBMS_DESCRIBE.DESCRIBE_PROCEDURE procedure and copy its output values to parameters supported by ODAC. For example, create the following types:

Code: Select all

CREATE TYPE tab_number AS TABLE OF NUMBER;
 
CREATE TYPE tab_varchar AS TABLE OF VARCHAR(1000);

Assign the following to the SQL property of TOraQuery component:

Code: Select all

DECLARE
  a_overload     DBMS_DESCRIBE.NUMBER_TABLE;
  a_position     DBMS_DESCRIBE.NUMBER_TABLE;
  a_level        DBMS_DESCRIBE.NUMBER_TABLE;
  a_arg_name     DBMS_DESCRIBE.VARCHAR2_TABLE;
  a_dty          DBMS_DESCRIBE.NUMBER_TABLE;
  a_def_val      DBMS_DESCRIBE.NUMBER_TABLE;
  a_mode         DBMS_DESCRIBE.NUMBER_TABLE;
  a_length       DBMS_DESCRIBE.NUMBER_TABLE;
  a_precision    DBMS_DESCRIBE.NUMBER_TABLE;
  a_scale        DBMS_DESCRIBE.NUMBER_TABLE;
  a_radix        DBMS_DESCRIBE.NUMBER_TABLE;
  a_spare        DBMS_DESCRIBE.NUMBER_TABLE;
  i              BINARY_INTEGER;
BEGIN
  DBMS_DESCRIBE.DESCRIBE_PROCEDURE(: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);
 
  :arg_name := tab_varchar();
  :arg_name . extend(a_arg_name.count);
  :dty      := tab_number();
  :dty . extend(a_dty.count);
  FOR i IN a_arg_name.first .. a_arg_name.last LOOP
    :arg_name(i) := a_arg_name(i);
    :dty(i)      := a_dty(i);
  END LOOP;
END;

Set the DataType property of ARG_NAME and DTY parameters to ftTable.

[jfudickar]

ODAC does not support PL/SQL table parameters.

Will there be a change for this.

PL/SQL Tables are common constructs and i (and oracle in SYS-Packages) use them in a often situations.

For my tool there is no way to define your database types, the tool is independent of any non oracle database objects.

And in my old components there was no problem to use plsql tables.

Please, this is very urgent for me.

Greetings and thanks
Jens

[jfudickar]

Is there a way to detect if there are warnings after execution of a sql-statement, for example after creating a package, that there are compiling errors?

Greetings
Jens

[Plash]

There is another way to call a stored procedure with PL/SQL table parameters. Set to True the Table property of the TOraParam objects that represents the PL/SQL table parameters. Also set the Length property of TOraParam to size of the table. If the stored procedure returns a table with larger size than value of the Length property, an exception will be raised.
You can use the TOraStoredProc component to call the DBMS_DESCRIBE.DESCRIBE_PROCEDURE. The component will create parameters with the Table property set to True and the Length property set to 1. You need to change the Length property for all table parameters to the corresponding value.
See also PLSQLTable ODAC demo.

[Plash]

ODAC raises exception when a statement is executed with compiling errors.