ADO.net using ODBC and oracle

[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

 

[Roy Fine]

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.Procedurenameoutval);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]

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

 

[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.

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

regards
roy fine