Re: REF Cursor returned across db link?

From: Dave Hau <davehau_nospam_123_at_nospam_netscape.net>
Date: Thu, 09 Oct 2003 20:40:45 GMT
Message-ID: <3F85C7C7.6040409_at_nospam_netscape.net>


[Quoted] [Quoted] 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 Thu Oct 09 2003 - 22:40:45 CEST

Original text of this message