Re: how to use a ref cursor in a datablock?

Heiko wrote:

> Hi, I finaly managed to create a package with a function which returns
> a ref cursor. I'd like to use this result/ref cursor in a datablock.
>
> How can I "link" the datablock to this result? By a trigger, or rather
> by any properties?
>
> Thanks for your help, anyway the code below, maybe it can help...
>
> Heiko Kopitzki
>
> ------CODE-------
>
> CREATE OR REPLACE PACKAGE ref_cursor_package AS
> Type sys_refcursor is Ref Cursor;
> FUNCTION tesfun( prodid_in IN INTEGER ) RETURN sys_refcursor;
> END ref_cursor_package;
> /
> show errors
>
> CREATE OR REPLACE PACKAGE BODY ref_cursor_package AS
> FUNCTION tesfun( prodid_in IN INTEGER ) RETURN sys_refcursor IS
> v_cur sys_refcursor;
> begin
> open v_cur for
> SELECT client.custid, client.name, client.address FROM client,
> produit, ligne_comm, commande
> WHERE client.custid = commande.custid
> AND commande.ordid = ligne_comm.ordid
> AND produit.prodid = prodid_in;
> RETURN v_cur;
> end tesfun;
> END ref_cursor_package;
> /
> show errors

First off, are you talking about Oracle Forms or some other product when you say link a ref cursor to a data block?

And if you are ... what version.

If you want us to guess you have to send each of us a coin to flip as we can't afford our own. Make mine a Kruggerand.

If I assume you are talking about Forms then the second item is that you posted to the wrong forum. You should post to comp.databases.oracle.tools.

And, having said all of that ...

(A) It would be better if your REF CURSOR was an OUT parameter in a stored procedure rather than a function
and
(B) Use the data block wizard and choose STORED PROCEDURE. You will need separate procedures for Insert, Update, and Delete.

Daniel Morgan Received on Wed May 29 2002 - 15:54:04 CDT