Re: REF Cursor returned across db link?

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_at_nospam_netscape.net> wrote in message news:<3F849988.1070103_at_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_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
Received on Thu Oct 09 2003 - 12:15:10 CDT