Yes, same error. I will try the DSQL at some point today and post the results.
Dave Hau <davehau_nospam_123_at_nospam_netscape.net> wrote in message news:<3F85C7C7.6040409_at_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_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 Fri Oct 10 2003 - 12:17:43 CDT
