PACKAGES, TYPES and STORED PROCEDURES

Hi

Hopefully the answer to this is really simple.

I have a package (myPackage) which has two types declared in it: one a record (say type A) and the other a ref cursor of that record (say, type B is ref cursor return A).

I have a function which returns the ref cursor from the package:

     myFunction (...) returns myPackage.B as ...

Now I have an OCI application which builds up and then executes PL/SQL blocks.

The PL/SQL I have built by hand is:

     declare output myPackage.B;
     begin
        :output = myFunction(...);
     end;

This is bound, prepared and executed OK but my problem is how to generate this PL/SQL block as I cannot find a reference to "B" in the system catalogues. I have found that the all_arguments catalogue will give me "myPackage" and that another one will give me "A" but it appears that the only way I can get "B" is to use all_source and parse the text returned. This is unacceptable/undesirable in terms of performance of my application.

I am using Oracle 9.2.0.1.0 on Windows and the coding I've done for the packages etc has been done through the enterprise manager, which hasn't complained, though if I've done something wrong please tell me.

I've trawled through the 9i database reference manual, both searching on package and just looking/reading, but I've been able to find what I need. I hope someone knows!

TIA
Alex Received on Wed Feb 05 2003 - 18:06:31 CET