REF Cursor returned across db link?

  • Thread starter Thread starter Matthew Houseman
  • Start date 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
 
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
 
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...
 
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...
 
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...
 
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
 
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...
 
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
| > >>
|
 
Back
Top