REF Cursor returned across db link?

**Dave Hau** · Jun 27 '08, 05:20 PM

Re: REF Cursor returned across db link?

ORA-00604 is usually followed by a more informative error. You need to
get that error to know what's going on.

try {
cmd.ExecuteNonQ uery()
}
catch ( OracleException e ) {
OracleError err1 = e.Errors[0];
OracleError err2 = e.Errors[1];

Console.WriteLi ne("Error 1 DataSource:", err1.DataSource );
Console.WriteLi ne("Error 1 Message:", err1.Message);
Console.WriteLi ne("Error 1 Number:", err1.Number);
Console.WriteLi ne("Error 1 Procedure:", err1.Procedure) ;
Console.WriteLi ne("Error 1 Source:", err1.Source);
Console.WriteLi ne("Error 2 DataSource:", err2.DataSource );
Console.WriteLi ne("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@I NSTANCE.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.Crea te_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.DataAcce ss.Client.Oracl eException 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

**Matthew Houseman** · Jun 27 '08, 05:20 PM

Re: REF Cursor returned across db link?

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_net scape.netwrote in message news:<3F849988. 1070103@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.ExecuteNonQ uery()
}
catch ( OracleException e ) {
OracleError err1 = e.Errors[0];
OracleError err2 = e.Errors[1];
>
Console.WriteLi ne("Error 1 DataSource:", err1.DataSource );
Console.WriteLi ne("Error 1 Message:", err1.Message);
Console.WriteLi ne("Error 1 Number:", err1.Number);
Console.WriteLi ne("Error 1 Procedure:", err1.Procedure) ;
Console.WriteLi ne("Error 1 Source:", err1.Source);
Console.WriteLi ne("Error 2 DataSource:", err2.DataSource );
Console.WriteLi ne("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@I NSTANCE.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.Crea te_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.DataAcce ss.Client.Oracl eException 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

**Dave Hau** · Jun 27 '08, 05:21 PM

Re: REF Cursor returned across db link?

Did you remember to put "BEGIN" and "END;" around the procedure call?

String query = " BEGIN API_MYLINK.Crea te_Invoice(:p1, :p2); END; ";

If so and it still fails, try using dynamic PL/SQL instead:

" BEGIN EXECUTE IMMEDIATE ''BEGIN API_MYLINK.Crea te_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@nospam_net scape.netwrote in message news:<3F849988. 1070103@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.ExecuteNonQ uery()
>>}
>>catch ( OracleException e ) {
> OracleError err1 = e.Errors[0];
> OracleError err2 = e.Errors[1];
>>
> Console.WriteLi ne("Error 1 DataSource:", err1.DataSource );
> Console.WriteLi ne("Error 1 Message:", err1.Message);
> Console.WriteLi ne("Error 1 Number:", err1.Number);
> Console.WriteLi ne("Error 1 Procedure:", err1.Procedure) ;
> Console.WriteLi ne("Error 1 Source:", err1.Source);
> Console.WriteLi ne("Error 2 DataSource:", err2.DataSource );
> Console.WriteLi ne("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@I NSTANCE.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.C reate_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.DataA ccess.Client.Or acleException 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

>>

**Dave Hau** · Jun 27 '08, 05:21 PM

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.Crea te_Invoice(:p1, :p2); END; ";
>
If so and it still fails, try using dynamic PL/SQL instead:
>
" BEGIN EXECUTE IMMEDIATE ''BEGIN API_MYLINK.Crea te_Invoice(:p1,
:p2);''; END; ";

Sorry there's a typo, should be:

" BEGIN EXECUTE IMMEDIATE '' BEGIN API_MYLINK.Crea te_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@nospam_net scape.netwrote in message
>news:<3F849988 .1070103@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.ExecuteNonQ uery()
>>}
>>catch ( OracleException e ) {
>> OracleError err1 = e.Errors[0];
>> OracleError err2 = e.Errors[1];
>>>
>> Console.WriteLi ne("Error 1 DataSource:", err1.DataSource );
>> Console.WriteLi ne("Error 1 Message:", err1.Message);
>> Console.WriteLi ne("Error 1 Number:", err1.Number);
>> Console.WriteLi ne("Error 1 Procedure:", err1.Procedure) ;
>> Console.WriteLi ne("Error 1 Source:", err1.Source);
>> Console.WriteLi ne("Error 2 DataSource:", err2.DataSource );
>> Console.WriteLi ne("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@I NSTANCE.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.C reate_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.DataA ccess.Client.Or acleException 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
>>>
>>>

>

**Thomas Kine** · Jun 27 '08, 05:21 PM

Re: REF Cursor returned across db link?

mhousema@ix.net com.com (Matthew Houseman) wrote in message news:<73986c9d. 0310081425.4146 5b34@posting.go ogle.com>...

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@I NSTANCE.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.Crea te_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.DataAcce ss.Client.Oracl eException 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

I do not believe that you can return a cursor across a DB link. I
have had similar problems trying to return a piplined table from a
remote database.

I created a GLOBAL TEMPORARY TABLE on the remote database, populated
it from the remote stored procedure, and then opened a cursor on the
GLOBAL TEMPORARY TABLE from the local database. Oracle manages the
creation and destruction of the table, and performance is acceptable
for my application.

HTH

**Matthew Houseman** · Jun 27 '08, 05:21 PM

Re: REF Cursor returned across db link?

Yes, same error. I will try the DSQL at some point today and post the results.

Dave Hau <davehau_nospam _123@nospam_net scape.netwrote in message news:<3F85C7C7. 6040409@nospam_ netscape.net>.. .

Did you remember to put "BEGIN" and "END;" around the procedure call?
>
String query = " BEGIN API_MYLINK.Crea te_Invoice(:p1, :p2); END; ";
>
If so and it still fails, try using dynamic PL/SQL instead:
>
" BEGIN EXECUTE IMMEDIATE ''BEGIN API_MYLINK.Crea te_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@nospam_net scape.netwrote in message news:<3F849988. 1070103@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.ExecuteNonQ uery()
>}
>catch ( OracleException e ) {
OracleError err1 = e.Errors[0];
OracleError err2 = e.Errors[1];
>
Console.WriteLi ne("Error 1 DataSource:", err1.DataSource );
Console.WriteLi ne("Error 1 Message:", err1.Message);
Console.WriteLi ne("Error 1 Number:", err1.Number);
Console.WriteLi ne("Error 1 Procedure:", err1.Procedure) ;
Console.WriteLi ne("Error 1 Source:", err1.Source);
Console.WriteLi ne("Error 2 DataSource:", err2.DataSource );
Console.WriteLi ne("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@I NSTANCE.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.Cr eate_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.DataAc cess.Client.Ora cleException 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
>

REF Cursor returned across db link?

REF Cursor returned across db link?

Comment

Comment

Comment

Comment

Comment

Comment