473,231 Members | 1,865 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

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 US***********@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
Jul 19 '05 #1
6 18919
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 US***********@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


Jul 19 '05 #2
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>...
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 US***********@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

Jul 19 '05 #3
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@nospam_netscape.net> wrote 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.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 US***********@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


Jul 19 '05 #4
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@nospam_netscape.net> wrote 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.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 US***********@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


Jul 19 '05 #5
mh******@ix.netcom.com (Matthew Houseman) wrote in message news:<73**************************@posting.google. com>...
All,

I've created a synonym that points to a package over a database link
like so:
CREATE SYNONYM API_MYLINK FOR US***********@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
Jul 19 '05 #6
Yes, same error. I will try the DSQL at some point today and post the results.
Dave Hau <davehau_nospam_123@nospam_netscape.net> wrote 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.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@nospam_netscape.net> wrote 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.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 US***********@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

Jul 19 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Jim Garrison | last post by:
Oracle has a tech article describing how a Java stored procedure can return a REF CURSOR: http://www.oracle.com/technology/sample_code/tech/java/codesnippet/jdbc/refcur/index.html Here's the...
9
by: Chris Michael | last post by:
I am using the following class in a CSS document: ..loginsubmit { background-color: #ffffff; FONT-WEIGHT: bold; color: #002980; cursor: hand } It works perfectly well, but it won't validate...
4
by: Bernard Dhooghe | last post by:
To retrieve data from a query where multiple rows can be returned, a cursor can be used. Different programming interface exist for cursors: embedded SQL, CLI, SQL PL, SQLJ, JDBC. I we look at...
3
by: Wayne Wengert | last post by:
In VB.NET is there a way to set the mouse cursor to the Wait cursor that will stay in effect across several forms? For example, I want to set the cursor to the WaitCursor in formA just before it...
1
by: riscy | last post by:
I'm having odd behaviour of VS 2003 pro recently. It happen when Dell 9200 returned from repairs with new motherboard to fix audio out socket problem (as they say). I installed Visual Assist X and...
2
by: Bill_DBA | last post by:
I have the following stored procedure that is called from the source of a transformation in a DTS package. The first parameter turns on PRINT debug messages. The second, when equals 1, turns on the...
1
by: vunet.us | last post by:
Mozilla reported the fix to this bug: https://bugzilla.mozilla.org/show_bug.cgi?id=167801. When input text field is located over div, the cursor cannot be seen unless special CSS properties are...
5
by: mike_dba | last post by:
I am looking for comments on experience using a Load from Cursor across multiple db's databases. I have a multi-terrabyte database across many partitions that includes a large table (1 Tb+). The...
6
by: Matthew Houseman | last post by:
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...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.