REF Cursor returned across db link? | |
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 | | | | 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.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: Quote:
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
| | | | 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_netscape.netwrote in message news:<3F849988.1070103@nospam_netscape.net>... Quote:
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: Quote:
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
| | | | re: REF Cursor returned across db link?
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: Quote:
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.netwrote in message news:<3F849988.1070103@nospam_netscape.net>...
> Quote:
>>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:
>> Quote:
>>>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
>>
| | | | re: REF Cursor returned across db link?
Dave Hau wrote: Quote:
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 Quote:
>
>
HTH,
Dave
>
>
>
>
>
>
Matthew Houseman wrote:
> Quote:
>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.netwrote in message
>news:<3F849988.1070103@nospam_netscape.net>...
>> Quote:
>>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
>>>
>>>
>
| | | | re: REF Cursor returned across db link?
mhousema@ix.netcom.com (Matthew Houseman) wrote in message news:<73986c9d.0310081425.41465b34@posting.google. com>... Quote:
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
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 | | | | 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_netscape.netwrote in message news:<3F85C7C7.6040409@nospam_netscape.net>... Quote:
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: Quote:
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.netwrote in message news:<3F849988.1070103@nospam_netscape.net>... Quote:
>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
>
|  |
Similar Oracle Database bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,327 network members.
|
