Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!prodigy.com!newsfeed.telusplanet.net!newsfeed.telus.net!snoopy.risq.qc.ca!newshub.northeast.verio.net!verio!newspeer.monmouth.com!newsfeed.icl.net!newsfeed.fjserv.net!skynet.be!skynet.be!fu-berlin.de!postnews1.google.com!not-for-mail
From: mhousema@ix.netcom.com (Matthew Houseman)
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 10:17:43 -0700
Organization: http://groups.google.com
Lines: 92
Message-ID: <73986c9d.0310100917.a102848@posting.google.com>
References: <73986c9d.0310081425.41465b34@posting.google.com> <3F849988.1070103@nospam_netscape.net> <73986c9d.0310090915.39b33fe3@posting.google.com> <3F85C7C7.6040409@nospam_netscape.net>
NNTP-Posting-Host: 216.35.131.141
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1065806263 27284 127.0.0.1 (10 Oct 2003 17:17:43 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 10 Oct 2003 17:17:43 +0000 (UTC)
Xref: newssvr20.news.prodigy.com comp.databases.oracle:23225 comp.databases.oracle.server:245054 comp.databases.oracle.misc:132196 comp.databases.oracle.tools:83125

Yes, same error.  I will try the DSQL at some point today and post the results.


Dave Hau <davehau_nospam_123@nospam_netscape.net> wrote in message news:<3F85C7C7.6040409@nospam_netscape.net>...
> Did you remember to put "BEGIN" and "END;" around the procedure call?
> 
> String query = " BEGIN API_MYLINK.Create_Invoice(:p1, :p2); END; ";
> 
> If so and it still fails, try using dynamic PL/SQL instead:
> 
> " BEGIN EXECUTE IMMEDIATE ''BEGIN API_MYLINK.Create_Invoice(:p1, 
> :p2);''; END; ";
> 
> 
> HTH,
> Dave
> 
> 
> 
> 
> 
> 
> Matthew Houseman wrote:
> > The 2nd message is:
> > ORA-00900: invalid SQL statement
> > 
> > which leads me to believe that ref cursors can't be returned across a
> > database link.  When I connect directly to the database schema that
> > contains the stored procedures, they all work great and return ref
> > cursors as one would expect.  Based on business process requirements,
> > though it would be best to get these stored procedures to return ref
> > cursors over the database link.
> > 
> > Dave Hau <davehau_nospam_123@nospam_netscape.net> wrote in message news:<3F849988.1070103@nospam_netscape.net>...
> > 
> >>ORA-00604 is usually followed by a more informative error.  You need to 
> >>get that error to know what's going on.
> >>
> >>try {
> >>   cmd.ExecuteNonQuery()
> >>}
> >>catch ( OracleException e ) {
> >>   OracleError err1 = e.Errors[0];
> >>   OracleError err2 = e.Errors[1];
> >>
> >>   Console.WriteLine("Error 1 DataSource:", err1.DataSource);
> >>   Console.WriteLine("Error 1 Message:", err1.Message);
> >>   Console.WriteLine("Error 1 Number:", err1.Number);
> >>   Console.WriteLine("Error 1 Procedure:", err1.Procedure);
> >>   Console.WriteLine("Error 1 Source:", err1.Source);
> >>   Console.WriteLine("Error 2 DataSource:", err2.DataSource);
> >>   Console.WriteLine("Error 2 Message:", err2.Message);
> >>...
> >>}
> >>
> >>
> >>HTH,
> >>Dave
> >>
> >>
> >>
> >>Matthew Houseman wrote:
> >>
> >>>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
> >>