Problem returning PL/SQL table types

Hi,

I had a form with two blocks in it I wanted to enter details in the first (IN) in order to query the second (OUT). The fields between the two blocks were different so basing the OUT block on a stored procedure seemed like hassle as I suppose I'd have had to include all the fields from the IN block in order to pass them in. The query on the out block also had to be dynamic so I created a procedure using the DBMS_SQL package to take the values from the first block create the appropriate query and fetch the results into a PL/SQL table which would be an OUT parameter. The PL/SQL table is declared with explicit datatypes not table%rowtype if this makes a difference. I wanted to use this same procedure in a few different forms so storing it on the database was the plan.

My problem is this - I now have a package with a PL/SQL table declared above the procedure which returns it and this compiles fine, what I can't do though is call the procedure from a form. I've tried declaring the pl/sql table type almost everywhere, in the package, in the call, in both, and it always complains that the supplied parameters are of the wrong type. I've tried it as an IN OUT parameter and couldn't get this to work either. How on earth do you have a PL/SQL table as an OUT parameter ? According to my manuals it's possible in a procedure but not a function.

In the example above I've had to put the procedure in the form and return the results direct to the block, but because I wanted to reuse the code I'd like to be able to crack this problem and get it as a stored package/procedure.

Any help would be great - it's driving me nuts now. TIA, Jacqui
Jacqui.Spencer_at_its.lancscc.gov.uk Received on Thu Nov 30 2000 - 12:44:32 CET