Print Thread
Calling A Packaged Procedure that returns a Table of Number as result
#85 10/08/99 06:37 PM
Joined: Oct 1999
Posts: 10
Rotterdam, ZH, Netherlands
J
Member
OP Offline
Member
J
Joined: Oct 1999
Posts: 10
Rotterdam, ZH, Netherlands
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


JohaViss
Re: Calling A Packaged Procedure that returns a Table of Number as result
#86 10/12/99 10:20 PM
Joined: Aug 1999
Posts: 22,217
Member
Offline
Member
Joined: Aug 1999
Posts: 22,217
To call dbms_describe.describe_procedure you need to use a TOracleQuery with the following PL/SQL Block:

[quote]
Code
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;
[/quote]

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:

[quote]
Code
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;
[/quote]


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

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


Marco Kalter
Allround Automations
Re: Calling A Packaged Procedure that returns a Table of Number as result
#87 10/15/99 03:03 PM
Joined: Oct 1999
Posts: 10
Rotterdam, ZH, Netherlands
J
Member
OP Offline
Member
J
Joined: Oct 1999
Posts: 10
Rotterdam, ZH, Netherlands
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


JohaViss

Moderated by  support 

Link Copied to Clipboard
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Release build 20200307)
Responsive Width:

PHP: 7.1.33 Page Time: 0.028s Queries: 12 (0.008s) Memory: 2.5091 MB (Peak: 3.0418 MB) Data Comp: Off Server Time: 2024-05-13 21:16:38 UTC
Valid HTML 5 and Valid CSS