REF Cursor returned across db link?

  • Thread starter Matthew Houseman
  • Start date
M

Matthew Houseman

All,

I've created a synonym that points to a package over a database link
like so:
CREATE SYNONYM API_MYLINK FOR (e-mail address removed)

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
 
D

Dave Hau

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
 
M

Matthew Houseman

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 said:
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 said:
All,

I've created a synonym that points to a package over a database link
like so:
CREATE SYNONYM API_MYLINK FOR (e-mail address removed)

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
Click to expand...
Click to expand...
 
D

Dave Hau

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 said:
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 said:
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 said:
All,

I've created a synonym that points to a package over a database link
like so:
CREATE SYNONYM API_MYLINK FOR (e-mail address removed)

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
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Hau

Dave said:
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; ";
Click to expand...

Sorry there's a typo, should be:

" BEGIN EXECUTE IMMEDIATE '' BEGIN API_MYLINK.Create_Invoice:)p1, :p2);
END; ''; END; ";


- Dave



HTH,
Dave






Matthew said:
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.

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 (e-mail address removed)

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
Click to expand...
Click to expand...
Click to expand...
 
T

Thomas Kine

All,

I've created a synonym that points to a package over a database link
like so:
CREATE SYNONYM API_MYLINK FOR (e-mail address removed)

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
Click to expand...

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
 
M

Matthew Houseman

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


Dave Hau said:
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 said:
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 said:
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 (e-mail address removed)

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
Click to expand...
Click to expand...
Click to expand...
 
K

Kevin Sun [MS]

Hi Matt,

Due to the complexity of this issue, if you still have problem regarding
this problem, I suggest to contact Microsoft tech-support by email or
phone. They will gladly help you further on this problem.

Sincerely,

Kevin
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! - www.microsoft.com/security

--------------------
| From: (e-mail address removed) (Matthew Houseman)
| Newsgroups:
microsoft.public.dotnet.framework.adonet,comp.databases.oracle,comp.database
s.oracle.server,comp.databases.oracle.misc,comp.databases.oracle.tools
| Subject: Re: REF Cursor returned across db link?
| Date: 10 Oct 2003 10:17:43 -0700
| Organization: http://groups.google.com
| Lines: 92
| Message-ID: <[email protected]>
| References: <[email protected]>
<3F849988.1070103@nospam_netscape.net>
<[email protected]>
<3F85C7C7.6040409@nospam_netscape.net>
| NNTP-Posting-Host: 216.35.131.141
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1065806263 27284 127.0.0.1 (10 Oct 2003
17:17:43 GMT)
| X-Complaints-To: (e-mail address removed)
| NNTP-Posting-Date: Fri, 10 Oct 2003 17:17:43 +0000 (UTC)
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!newsfeed.freenet.de!news-feed1.de1.concert.net!fu-berlin.de!postnews1.g
oogle.com!not-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:63382
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| Yes, same error. I will try the DSQL at some point today and post the
results.
|
|
| > 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.
| > >
| > >
| > >>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 (e-mail address removed)
| > >>>
| > >>>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
| > >>
|
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ODP.NET, stored procedures and database links 3
Orphan REF cursor returned to VB.NET from Oracle PL/SQL stored procedure? 4
DbProviderFactory, Oracle and a ref cursor 3
REF CURSOR returns no rows 5
call oracle ref cursor in vb.net to grid or any bound controls 1
Oracle REF Cursors and OracleDataAdapter - data not populating 2
Unsupported Oracle Datatype - Ref Cursor dont work ! 8
Call SP with application block (daab) & multiple out REF CURSOR parameters 2

Top