ODP.NET, stored procedures and database links

[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# and attempt to dispatch a stored
procedure using the synonym like so: API_MYLINK.Create_Invoice

I get the following exception raised:
Oracle.DataAccess.Client.OracleException ORA-00604: error occurred at
recursive SQL level 1

Is what I'm trying to do even possible and if so, can someone provide
a code snippet demonstrating the dispatch of a stored procedure over a
database link.

Thanks,
Matt Houseman

 

[Kevin Yu]

Hi Matthew,

I think that the problem might locates in the Oracle database server.
Please check if the link with the two database servers is fine. And try to
run your stored procedure in SQL plus in Oracle.

Kevin Yu
=========
This post is provided "AS IS" with no warranties, and confers no rights.
--------------------
| From: (e-mail address removed) (Matthew Houseman)
| Newsgroups: microsoft.public.dotnet.framework.adonet
| Subject: ODP.NET, stored procedures and database links
| Date: 25 Sep 2003 18:20:36 -0700
| Organization: http://groups.google.com/
| Lines: 22
| Message-ID: <[email protected]>
| NNTP-Posting-Host: 216.35.131.141
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1064539237 31650 127.0.0.1 (26 Sep 2003
01:20:37 GMT)
| X-Complaints-To: (e-mail address removed)
| NNTP-Posting-Date: 26 Sep 2003 01:20:37 GMT
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!sn-xit-03!sn-xit-06!sn-
xit-08!sn-xit-09!supernews.com!postnews1.google.com!not-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:62176
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| 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# and attempt to dispatch a stored
| procedure using the synonym like so: API_MYLINK.Create_Invoice
|
| I get the following exception raised:
| Oracle.DataAccess.Client.OracleException ORA-00604: error occurred at
| recursive SQL level 1
|
| Is what I'm trying to do even possible and if so, can someone provide
| a code snippet demonstrating the dispatch of a stored procedure over a
| database link.
|
| Thanks,
| Matt Houseman
|

 

[Matt Houseman]

Kevin,

Yes, that's exactly what I was thinking and the dispatch
of the SP over the DB Link worked great from SQL*Plus.
Perhaps the problem is in ODP.NET?

Do you know of anyone who has tried to call an SP over a
DB Link in C# using ODP.NET?

The workaround of course is to code a wrapper stored
procedure in the local database that I'm attached to then
make the remote call from PL/SQL which mimics the
successful dispatch from SQL*Plus.

Thanks,

Matt

 

[Lyndon Hills]

On Fri, 26 Sep 2003 08:43:51 -0700, "Matt Houseman"

This error has been seen to be caused by a lack of sessions or
processes available to the server. These are variables that are set at
the database level in init.ora. Try upping them and restarting. Also
try posting to the Oracle forum for ODP.net. There may be things that
you can use in your connect string. Also which version of ODP.net?
there have been a number of versions, the current one available is a
beta, but it's been used for a while and seems to have fixed problems
for some people, without introducing new ones!