Re: REF Cursor returned across db link?

Dave Hau wrote:
> 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; ";

Sorry there's a typo, should be:

" BEGIN EXECUTE IMMEDIATE '' BEGIN API_MYLINK.Create_Invoice(:p1, :p2); END; ''; END; ";

• Dave

>
>
> 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
>>>
>>>