Re: REF Cursor returned across db link?

mhousema_at_ix.netcom.com (Matthew Houseman) wrote in message news:<73986c9d.0310081425.41465b34_at_posting.google.com>...
> All,
>
> I've created a synonym that points to a package over a database link
> like so:
> CREATE SYNONYM API_MYLINK FOR USER.CSAPI_V2_at_INSTANCE.DOMAIN.COM
>
> I've granted execute like so:
> grant execute on CSAPI_V2 to scott;
>
> When I attach to the database in C# using ODP.NET and attempt to
> dispatch a stored procedure using the synonym like so:
> API_MYLINK.Create_Invoice
>
> Also, there are two parameters to the Create_Invoice stored procedure:
> 1) is an input parameter of type number 2) is an output parameter of
> type ref cursor.
>
> I get the following exception raised:
> Oracle.DataAccess.Client.OracleException ORA-00604: error occurred at
> recursive SQL level 1
>
> Is it possible to return a REF CURSOR over a DB LINK and if so, can
> someone provide a code snippet demonstrating the dispatch of a stored
> procedure over a
> database link that returns a REF CURSOR.
>
> Thanks,
> Matt Houseman

I do not believe that you can return a cursor across a DB link. I have had similar problems trying to return a piplined table from a remote database.

I created a GLOBAL TEMPORARY TABLE on the remote database, populated it from the remote stored procedure, and then opened a cursor on the GLOBAL TEMPORARY TABLE from the local database. Oracle manages the creation and destruction of the table, and performance is acceptable for my application.

HTH Received on Fri Oct 10 2003 - 07:51:37 CDT