473,324 Members | 1,678 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,324 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 18941
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: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.