Re: Problem returning PL/SQL table types

Hi,
Try to use the way Forms provides:
1. Set the second block based on a view or table. 2. The first block seems to be a control block. You can add a push button on this block, and in the trigger, construct the where_clause string based on the input values.
write other codes :
...
SET_BLOCK_PROPERTY('THE_SECOND_BLOCK_NAME', DEFAULT_WHERE, where_clause); GO_BLOCK('THE_SECOND_BLOCK_NAME');
EXECUTE_QUERY; ...
END; That's all.

ml

"Jacqui Spencer" <jacqui.spencer_at_lancscc.gov.uk> wrote in message news:3a264b38$1_at_news.norweb.net...
> 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 Fri Dec 01 2000 - 02:15:12 CET