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 - 15:40:45 CDT
