Re: REF Cursor returned across db link?
From: Kevin Sun [MS] <zhisun_at_onlinemicrosoft.com>
Date: Mon, 13 Oct 2003 07:27:10 GMT
Message-ID: <RgOcwtVkDHA.1928_at_cpmsftngxa06.phx.gbl>
| From: mhousema_at_ix.netcom.com (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: <73986c9d.0310100917.a102848_at_posting.google.com>
| References: <73986c9d.0310081425.41465b34_at_posting.google.com>
| 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: groups-abuse_at_google.com
| 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.
|
|
| Dave Hau <davehau_nospam_123_at_nospam_netscape.net> wrote in message
news:<3F85C7C7.6040409_at_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_at_nospam_netscape.net> wrote in message
news:<3F849988.1070103_at_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 USER.CSAPI_V2_at_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
| > >>
|
Received on Mon Oct 13 2003 - 09:27:10 CEST
Date: Mon, 13 Oct 2003 07:27:10 GMT
Message-ID: <RgOcwtVkDHA.1928_at_cpmsftngxa06.phx.gbl>
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: mhousema_at_ix.netcom.com (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: <73986c9d.0310100917.a102848_at_posting.google.com>
| References: <73986c9d.0310081425.41465b34_at_posting.google.com>
<3F849988.1070103_at_nospam_netscape.net> <73986c9d.0310090915.39b33fe3_at_posting.google.com> <3F85C7C7.6040409_at_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: groups-abuse_at_google.com
| 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.
|
|
| Dave Hau <davehau_nospam_123_at_nospam_netscape.net> wrote in message
news:<3F85C7C7.6040409_at_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_at_nospam_netscape.net> wrote in message
news:<3F849988.1070103_at_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 USER.CSAPI_V2_at_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
| > >>
|
Received on Mon Oct 13 2003 - 09:27:10 CEST