Re: ODP.NET, stored procedures and database links

Oh, I forgot to add one more piece of information and that is the 1st parameter is an input parameter that 'drives' the output of the 2nd parameter which is a REFCURSOR. I believe that the REFCURSOR across the database link is causing the "ORA-00604: error occurred at recursive SQL level 1". The second error message is: "ORA-00900: invalid SQL statement"

This stored procedure works like a champ when I'm connected to the database where the SP 'lives'. Due to business requirements though, this stored procedure must be dispatched across a database link or each client will have to maintain 2 database connections. Not exactly an earth-shattering bad thing, but less than optimal.

More precisely, does anyone know if REFCURSORS can be returned across database links?

Matt

Dave Hau <davehau_nospam_123_at_nospam_netscape.net> wrote in message news:<3F78D0C2.20307_at_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 Oct 06 2003 - 08:30:12 CDT