Oracle.DataAccess.Client : ORA00911

Topics: Data Access Application Block
Jun 13, 2008 at 1:18 AM

Hi,

I am currently using the DAAB with the Oracle.DataAccess dll (ODP for .Net), and am having problems when calling a stored procedure from with DAAB.

The stored procedure is very simple (just 4 parameters); which I can call in the standard way (see below):

 

OracleConnection

con;

 

 

OracleCommand cmd = null;

 

con =

new OracleConnection();

 

con.ConnectionString =

"Data Source=testsource;User Id=testuid;Password=testpwd;";

 

cmd =

new OracleCommand();

 

cmd.Connection = con;

cmd.CommandType =

CommandType.StoredProcedure;

 

cmd.CommandText =

"test";

 

cmd.Parameters.Add("id

", OracleDbType.Int16);

 

cmd.Parameters[0].Direction =

ParameterDirection.Input;

 

cmd.Parameters[0].Value =

"0";

 

cmd.Parameters.Add(

"fname", OracleDbType.Varchar2, 20);

 

cmd.Parameters[1].Direction =

ParameterDirection.Input;

 

cmd.Parameters[1].Value =

"";

 

cmd.Parameters.Add(

"sname", OracleDbType.Varchar2, 20);

 

cmd.Parameters[2].Direction =

ParameterDirection.Input;

 

cmd.Parameters[2].Value =

"";

 

cmd.Parameters.Add(

"description", OracleDbType.Varchar2, 200);

 

cmd.Parameters[3].Direction =

ParameterDirection.Input;

 

cmd.Parameters[3].Value =

"";

 

 

OracleDataAdapter da = new OracleDataAdapter(cmd);

 

 

DataSet DS = new DataSet();

 

da.Fill(DS);


This works fine, but when I try to implement this within the DAAB (see below), I keep getting an error message back saying :

System.Data.OracleClient.OracleException : ORA-00911: invalid character
%SQL-F-BAD_TOKEN, => is not a valid SQL language element


Which I belive means that the symbol '=>' is somehow being passed though.

The DAAB implementation of the call is:

 

Database

db = DatabaseFactory.CreateDatabase("testsource");

 

 

string sqlCommand = "test";

 

 

System.Data.Common.DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

 

db.AddInParameter(dbCommand,

"id", DbType.Int16, "0");

 

db.AddInParameter(dbCommand,

"fname", DbType.String, "");

 

db.AddInParameter(dbCommand,

"sname", DbType.String, "");

 

db.AddInParameter(dbCommand,

"description", DbType.String, "");

 

 

DataSet productDataSet = db.ExecuteDataSet(dbCommand);


I can succesfully retrieve tables from the database using the DAAB, but cannot call a stored procedure with parameters.

Additionally I have tried the same code (DAAB implementation) for calling a SQL Server stored procedure and it works fine, it is just with RDB via this provider (ODP); 

From what I can see the manual way (non DAAB) works because it is specifying specific oracle command parameters, but surely this is the point of the DAAB, i.e. to make the code generic so that any provider will work?


Any pointer would be greatly appreciated.


Simon 

 

Jul 10, 2008 at 11:02 AM
Hi Simon,

I was larking and I saw this thread. I also faced this issue and solved it. you may be solved urs by now. So I am replying this for others who might find it helpful.

If you sure about your stored proc, then you might check this out -

The is a issue with Oracle.DataAccess is that by default ODP command map parameters by position, means, you have to add parameter in same order as your SP takes. Like - if the SP DosomeThing(x,y,z) then you have to add parameter like - db.AddInParameter(x);db.AddInParameter(y);db.AddInParameter(z);

But you just customize the DAABs AddParameter method (by Overloading) in OracleDatabase.cs like this

default method: AddParameter(DbCommand com..)
{
 // Call the overloader method
AddParameter(com as OracleCommand..);

}

AddParameter(OracleCommad com.....)
{
// Just make this change in OracleCommand
// this will enable your command to map parameters by  Parameter names not by position
com.BindByNames=true;
}
Hope this will help

thanks
dj
Jul 10, 2008 at 11:49 PM
Hya,

Thanks for the response.

Im must apologise as my original email is misleading as it was

System.Data.OracleClient;

 

I was having problems with not Oracle ODP. Oracle ODP i got working with DAAB by installing 11g and then it worked.

Regarding your original suggestion I checked this (using MS ODP) and still have this message returned which is frustrating.

It is actually an RDB db with an OCI layer and maybe this is the problem?

I am going to try and get an Oracle db to test against, i.e. not Oracle RDB.

i'LL Let you know.

 

Simon

Jul 11, 2008 at 7:02 AM

Hi,

 

I tried calling a similar similar stored procedure in an Oracle db this time, i.e. not an RDB db and the code worked fine.

 

This must be something in the OCI layer then?

 

I will keep you updated.

 

Thanks