Dave Hau wrote:[color=blue]
> 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; ";[/color]
Sorry there's a typo, should be:
" BEGIN EXECUTE IMMEDIATE '' BEGIN API_MYLINK.Create_Invoice(:p1, :p2);
END; ''; END; ";
- Dave
[color=blue]
>
>
> HTH,
> Dave
>
>
>
>
>
>
> Matthew Houseman wrote:
>[color=green]
>> 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>...
>>[color=darkred]
>>> 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
>>>
>>>[/color][/color]
>[/color]
