Re: ODP.NET, stored procedures and database links

From: Dave Hau <davehau_nospam_123_at_nospam_netscape.net>
Date: Tue, 30 Sep 2003 00:39:31 GMT
Message-ID: <3F78D0C2.20307@nospam_netscape.net>

Try using an anonymous PL/SQL block for the command, and set command type as Text instead of StoredProcedure:

String block = " BEGIN " +

		" API_MYLINK.Create_Invoice(:p1, :p2); " +
		" END; ";

OracleCommand cmd = new OracleCommand();

cmd.Connection = con;
cmd.CommandText = block;
cmd.CommandType = CommandType.Text;

... (bind the parameters p1 and p2)
cmd.ExecuteNonQuery();

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# 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
Received on Mon Sep 29 2003 - 19:39:31 CDT