Parameterized OracleCommand/ Bind Variables for Updates to Oracle Table Using ODP.NET?

Question

I have an Oracle 11g table with the following column names and types:

ID VARCHAR2(32 BYTE)
VERSION VARCHAR2(12 BYTE)

I am trying to get bind variables to work in an Oracle parameterized command for Update statements on this table using ODP.NET and C#, but I'm having no luck. Here is the code I am using:

string constr = gconstr + "; Data Source=" + db;
OracleConnection con = new OracleConnection(constr);
con.Open();
ddiId = "WS_5043";
ddiVer = "1.0.3";

string UPDATE_CLOB_QUERY = "UPDATE :ddiTable SET version=:ddiVer WHERE id=:ddiId";

Oracle.DataAccess.Types.OracleXmlType ret;
OracleCommand oracleCommand2 = new OracleCommand(UPDATE_CLOB_QUERY, con);
oracleCommand2.Parameters.Clear();
oracleCommand2.Parameters.Add("ddiId", OracleDbType.Varchar2, ParameterDirection.Input).Value = ddiId;
oracleCommand2.Parameters.Add("ddiTable", OracleDbType.Varchar2, ParameterDirection.Input).Value = ddiTable;
oracleCommand2.Parameters.Add("ddiVer", OracleDbType.Varchar2, ParameterDirection.Input).Value = ddiVer;
oracleCommand2.ExecuteNonQuery();
oracleCommand2.Dispose();

When I run the above code in VS2010, I get the following Oracle error:

ORA-01036: illegal variable name/number

However, when I remove the bind variable declarations in the SQL then the above works i.e.

string UPDATE_CLOB_QUERY = "UPDATE wstable SET version='1.0.2' WHERE id='WS_5043'";

What am I doing wrong?

I need to know how to get bind variables and parameterized Oracle commands working with SQL Update commands such as the one above as a requirement, because I will begin eventually updating large clobs in other tables (and concatenating the clobs as strings in SQL will of course cause size limit errors). So getting this to work will be the first victory.

I did find [this answer elsewhere on the site][1] that may help you. [1]: stackoverflow.com/questions/1179652/… — mgnoonan
– mgnoonan, Commented Jan 8, 2013 at 22:37

Soner Gönül · Accepted Answer · 2013-01-08 22:48:12Z

2

In this line;

oracleCommand2.Parameters.Add("ddiTable", OracleDbType.Varchar2, ParameterDirection.Input).Value = ddiTable;

You actually try to add as a parameter your table name (ddiTable). You can't do that. And there is no point to add your query table name as a parameter. And If you want to use Parameters.Add() method with 3 parameters, you must use as parameters like;

public OracleParameter Add(
    string parameterName,
    OracleType dataType,
    int size
)

That's the only fits overloading method. There is no overloading for taking third parameter type of ParameterDirection.Input of Parameters.Add() method.

I need to know how to get bind variables and parameterized Oracle commands working with SQL Update commands such as the one above as a requirement.

May I suggest you to read these;

answered Jan 8, 2013 at 22:48

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fuzzy Analysis Over a year ago

Fantastic, thanks for taking the time to point me in the right direction.

Collectives™ on Stack Overflow

Parameterized OracleCommand/ Bind Variables for Updates to Oracle Table Using ODP.NET?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related