ASP.NET ODBC Oracle error “ORA-01008: not all variables bound” solved

The other day I decided to try my hands on using named parameters in a query to query an Oracle database using ODBC when I got this message:

ERROR [07001] [Microsoft][ODBC driver for Oracle][Oracle]
ORA-01008: not all variables bound

From what I’ve found, it looks like if you are querying an Oracle database using the System.Data.Odbc class, you can only use unnamed parameters (defined by "?"). In order to use named parameters, you’ll need to use the System.Data.OracleClient class instead (note you’ll need to add that reference manually to your project).

Here’s what I mean:

public DataTable queryDatabase_odbc () {
	OdbcConnection con = new OdbcConnection("Driver={Microsoft ODBC for Oracle};server=DBSERVER;uid=xxUserxx;pwd=xxPwdxx");
	con.Open();
	OdbcCommand ps = new OdbcCommand("alter session set current_schema = xxdbschemaxx", con);
	ps.Prepare();
	ps.ExecuteNonQuery();

	OdbcCommand ps2 = new OdbcCommand("select * from aTable where field = :AParam", con);
	ps2.Prepare();
	ps2.Parameters.Add("AParam",OdbcType.VarChar).Value="Some String Value";
	DataSet rs = new DataSet();
	OdbcDataAdapter da = new OdbcDataAdapter(ps2);
	da.Fill(rs);	// This will throw an exception
	return rs.Tables[0];
}

// The exact same code as above, just using the the OracleClient class
public DataTable queryDatabase_ora () {
	OracleConnection con = new OracleConnection("server=DBSERVER;uid=xxUserxx;pwd=xxPwdxx;");
	con.Open();
	OracleCommand ps = new OracleCommand("alter session set current_schema = xxdbschemaxx", con);
	ps.Prepare();
	ps.ExecuteNonQuery();

	OracleCommand ps2 = new OracleCommand("select * from aTable where field = :AParam", con);
	ps2.Prepare();
	ps2.Parameters.Add("AParam",OracleType.VarChar).Value="Some String Value";
	DataSet rs = new DataSet();
	OracleDataAdapter da = new OracleDataAdapter(ps2);
	da.Fill(rs);
	return rs.Tables[0];
}

However, I do have to point out there is one draw-back with using the OracleClient class – you can no longer use unnamed parameters

But in a way, you do get more out of using named parameters than unnamed parameters (like when you have a query looking up the same value on multiple fields). Besides, you can always (sort of) hack named parameters to act like unnamed parameters by using an integer string as name…


Here a page I found with details in using parameterized queries in different DBMS:
http://weblogs.asp.net/cibrax/archive/2006/09/28/Parameterized-Queries-_2800_Oracle_2C00_-SQLServer_2C00_-OleDb_2900_.aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: