Re: ADO.net using ODBC and oracle

From: Roy Fine (rlfine_at_twt.obfuscate.net)
Date: 05/15/04 

Date: Sat, 15 May 2004 09:33:26 -0400

Shania

Try changing the CommandType to Text, and using an anonymous pl/sql block.

Here is one attempt (albeit in C#)

string cnxtstr = "Data Source=xxx;User ID=xxxx;Password=xxxx;";
string cmdtxt = "begin packagename.Procedurename(:outval);end;";
object myValue = null;
using(OdbcConnection oCnxion = new OdbcConnection(cnxtstr)){
   oCnxion.Open();
   using(OdbcCommand cSqlOdbcCmd = new OdbcCommand(cmdtxt,oCnxion)){
      cSqlOdbcCmd.CommandType = CommandType.Text;
      OdbcParameter oParm1 =
cSqlOdbcCmd.Parameters.Add("outval",OdbcType.Decimal);
      oParm1.Direction = ParameterDirection.Output;
      cSqlOdbcCmd.ExecuteNonQuery();
      myValue = oParm1.Value;
   }
}

regards
roy fine

"shania" <uramnath@hotmail.com> wrote in message
news:795a47.0405140659.5a944c13@posting.google.com...
> Hi,
> I am trying to execute a simple oracle stored procedure that has an
> output parameter, using odbc and ado.net. Here is the code
>
> cSqlOdbcCmd = objConnect.CreateCommand()
> cSqlOdbcCmd.CommandType = CommandType.StoredProcedure
> cSqlOdbcCmd.CommandText = "packagename.Procedurename"
>
> cSqlOdbcCmd.Parameters.Add("outResult",
> OdbcType.Numeric).Direction
> = ParameterDirection.Output
>
>
> objConnect.Open()
> cSqlOdbcCmd.ExecuteNonQuery()
>
> The oracle client version is 8.05 and the server version is 8i
> (release 8.1.5). I keep getting an invalid sql statement. The same
> runs in vb6 ado (with similar call) just fine. I checked for
> packagename and procedure name mismatches but there were none. I have
> odbc set up propery along with net 8 client. Everytime it goes to the
> executenonquery statement it gives an invalid sql statement error. I
> also tried to execute simple text sql statments (not stored
> procedures) which did not have any problems.
>
> What could be wrong?
>
>
> Thanks

Relevant Pages

  • ADO.net using ODBC and oracle
    ... I am trying to execute a simple oracle stored procedure that has an ... odbc set up propery along with net 8 client. ... executenonquery statement it gives an invalid sql statement error. ... also tried to execute simple text sql statments (not stored ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: desc: invalid sql statement
    ... It is not part of the Oracle SQL language. ... Subject: desc: invalid sql statement ... I believe a describe statement is Oracle DDL, but I still failed for both of the ways that I know of to execute sql. ...
    (perl.dbi.users)
  • Execute stored procedure via web method.
    ... >From a vb.net application I'm want to execute an Oracle Stored Procedure via ... Oracle Command or the parameter objects object to the Webservice, ... How would I get the parameters to the web method? ...
    (microsoft.public.dotnet.framework.webservices)
  • Re: How to call Oracle package from DTS package?
    ... I can execute any Select statement successfully, but I don't know how to ... I'm getting an error: Invalid SQL statement. ... >> How to call an Oracle package from DTS? ...
    (microsoft.public.sqlserver.dts)
  • data view web part with custom query
    ... I would like to call an Oracle stored procedure from the Custom Query of a ... I have tried entering the SP name and "execute sp_name") but just get "the ... The SP executes fine in an Oracle environment, and the database is reachable ...
    (microsoft.public.frontpage.programming)