Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!news.maxwell.syr.edu!postnews1.google.com!not-for-mail
From: google@righteoussoftware.com (Thomas Kine)
Newsgroups: microsoft.public.dotnet.framework.adonet,comp.databases.oracle,comp.databases.oracle.server,comp.databases.oracle.misc,comp.databases.oracle.tools
Subject: Re: REF Cursor returned across db link?
Date: 10 Oct 2003 05:51:37 -0700
Organization: http://groups.google.com
Lines: 41
Message-ID: <b115fcd7.0310100451.49446faf@posting.google.com>
References: <73986c9d.0310081425.41465b34@posting.google.com>
NNTP-Posting-Host: 156.99.90.161
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1065790297 14107 127.0.0.1 (10 Oct 2003 12:51:37 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 10 Oct 2003 12:51:37 +0000 (UTC)
Xref: newssvr20.news.prodigy.com comp.databases.oracle:23224 comp.databases.oracle.server:244996 comp.databases.oracle.misc:132187 comp.databases.oracle.tools:83123

mhousema@ix.netcom.com (Matthew Houseman) wrote in message news:<73986c9d.0310081425.41465b34@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@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