Calling A Packaged Procedure that returns a Table of Number as result

[Johan Visser]

I'm trying to get a describe of a stored procedure using the 'DBMS_DESCRIBE' package.

I'm calling the procedure 'DESCRIBE_PROCEDURE' with the correct number of parameters but I get an error

"Wrong number or types of arguments in call to 'DESCRIBE_PROCEDURE'"

How can I use this package to get the arguments of a stored procedure.

------------------
JohaViss

 

[Marco Kalter]

To call dbms_describe.describe_procedure you need to use a TOracleQuery with the following PL/SQL Block:

begin
  dbms_describe.describe_procedure(object_name =>  :Object_name,
                                   reserved1 => null,
                                   reserved2 => null,
                                   overload =>  :Overload,
                                   position => :position,
                                   level => :level,
                                   argument_name => :argument_name,
                                   datatype => :datatype,
                                   default_value => :default_value,
                                   in_out => :in_out,
                                   length => :length,
                                   precision => :precision,
                                   scale => :scale,
                                   radix => :radix,
                                   spare => :spare);
end;

The object_name must be declared as string variable, the argument_name must be declared as a PL/SQL Table of string(30), and all other variables must be PL/SQL Tables of integer. The following Delphi code is an example of how to execute the query and display all parameter names of the dbms_job.submit procedure:

procedure TMyForm.DescribeButtonClick(Sender: TObject);
var Arguments: Variant;
    i: Integer;
begin
  with DescribeQuery do
  begin
    SetVariable('object_name', 'dbms_job.submit');
    Execute;
    Arguments := GetVariable('argument_name');
    for i := VarArrayLowBound(Arguments, 1) to VarArrayHighBound(Arguments, 1) do
      ShowMessage(Arguments[i]);
  end;
end;

------------------
Marco Kalter
Allround Automations

[This message has been edited by mkalter (edited 12 October 1999).]

 

[Johan Visser]

This solution works perfectly.

There are 2 things that can cause problems (as far as I can see for now)

1)
If you choose for a PL/SQL table, make sure that the tablesize is big enough to hold all the data. If you don't, Oracle returns a ORA-06512

2)
You must check if there is a value returned before you try to use the variant
This can be done with the following code:

if TVarData(Arguments).VType = varEmpty then
exit; // Or whatever you want to do here.

This must be done before the line:

for i := VarArrayLowBound(Arguments, 1) to VarArrayHighBound(Arguments, 1) do
.
.
.
.

------------------
JohaViss