By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,084 Members | 1,266 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,084 IT Pros & Developers. It's quick & easy.

REF Cursor returned across db link?

P: n/a
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
Share this Question
Share on Google+
6 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.