ADO.net using ODBC and oracle

  • Thread starter Thread starter shania
  • Start date Start date
S

shania

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
 
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
 
Thank you for your reply. I will try it out and go from there.
It does however work if I use the following syntax
{call packagename.procedurename(?)}
the ? being the output variable.


Thanks
 
shania said:
Thank you for your reply. I will try it out and go from there.
It does however work if I use the following syntax
{call packagename.procedurename(?)}
the ? being the output variable.
Click to expand...

That's the ODBC call syntax - with the {} -, and that works as well...

regards
roy fine
 
Back
Top