Re: Base a block on a stored Procedure

From: Geoff White <whiteg_at_ccis.adisys.com.au>
Date: Fri, 04 Jun 1999 10:51:07 +0800
Message-ID: <37573F1B.ABDB72FA@ccis.adisys.com.au>

tramseier_at_my-deja.com wrote:

> I try to base a block on the following procedure
> in a package:
>
> package ....
>
> type myintable IS TABLE OF NUMBER INDEX BY .....
> type myouttable IS TABLE OF <type..> INDEX
> BY....
>
> procedure a (pintable IN mytable, pouttable IN
> OUT myouttable);
>
> ...
>
> I can build the block but i have no idea how i
> could pass the parameter
>
> pintable (in the 'query data source arguments'
> property of the block).
>
> Is there a solution for this problem?
>
> thanks for any idea.
>
> Thomas Ramseier
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.

I have done this using a PL/SQL table. (NOTE - in Forms 5 the use of REF CURSORS is not supported (despite what is implied in the help). I have been told that they are supported in Forms 6). To use a PL/SQL table the steps you need to follow are:

.In the package:
. create a record type for the data you wish to return to the form
. create a PL/SQL table type of those records
. create a procedure which accepts a table of that type and populates

the table using a cursor and a simple loop. The table parameter should be IN OUT or OUT.

. In the Form:
. set the Query Data Source Type to procedure
. set the Query Data Source Name to the name of your package.procedure
. In the Query Data Source Columns list the names and types of the

columns in your record (which you use in the PL/SQL table) . In the Query Data Source Arguments put in a name for the table, set the Type to TABLE, set the type name to package.<PL/SQL table type>, set the mode to IN OUT.

That's it. Whew!

Using a REF CURSOR is a little easier because you don't need to set up the record, table, table loading stuff.

HTH. If you have any further questions regarding this feel free to email me.

Geoff Received on Thu Jun 03 1999 - 21:51:07 CDT