Tag Archives: Oracle Bind Variables

How to avoid ORA-01008: Not all variables bound & ORA-01036: illegal variable name/number while using with .net

When I started working with Oracle Bind Variables with Asp.net I encountered these 2 exceptions very frequently. I note down some my observations so that we can avoid these exceptions.

1) Try removing Semicolons at the end from query statements, some time its habitual to keep “;” at the end.

2) Verify, You are assigning values to the Bind Variables.

3) Verify, if you are passing parameters to the stored Procedures, they are exactly same as per requirements (order, type etc.).

4) Verify, if you are using select, then selected columns are actually in the table.

5) Verify, if your are defining the data type of the bind variable then check if the defined data type and actual column type mapping are correct.

6) If you’re using Microsoft – OracleHelper file to connect then not to mentioning the data type for bind variable is some times useful.

7) Some exception occurred due to the length of the data we are passing to parameter.

For Example – In past I come thought one experience in particular, I was creating Query dynamically depending upon the user input, generally I was passing the data in string format when generating the query that means query was treated as single string we passing to Oracle, But for longer Inputs system started showing me exceptions

“ORA-01036: illegal variable name/number” later found that Oracle limit query size to 4k, means if we are having the column of type CLOB/BLOB in DB and we are passing passing the parameter with data type as string with largest data in that string the query which is getting created eventually is more than 4k and we get the exception. In this case we need to use Bind Variables with no Oracle Data Type mentioned.

8) Remove the “:” from the prefix of the Bind Variable which is getting passed parameter of the stored procedure… it is not required when we are calling SP from Asp.net.

Using Oracle Bind Variables in Asp.net code = Performance + less memory Overhead

BIND Variables
Concept of Bind Variables often came across when we start developing .Net ( or any application ) with Oracle as database , mainly we can say its oracle concept for high performance.

To Understand the Concept of Bind Variables , we first need to understand how the Oracle is executing the Queries passed to it.All Queries passed to Oracle are passed to Shared Pool for creation of Execution plan we will take look at it in brief.Oracle Shared Pool contains Library Cache which contains Shared SQL Area works as buffer for SQL statements ,responsible for Collection,parsing,interpreting and execution of the SQL Statements passed to Oracle Database.Parsing Algorithm in this area works in such a ways that identical SQL statements dont getting parsed each time , they are getting parsed once, first time and next time execution plan is ready for them irrespective of users who initiate the query.In simple words Oracle is using the concept of the reusability.
Oracle uses a algorithm to assign a hash value to each SQL statement based on the characters in the SQL statement. Any change in a statement will need to created new HASH value and thus Oracle assumes it is a new statement. Each new statement must be verified, parsed,interpreted and have an execution plan generated and stored, all causes extra memory overhead, and if we are using the bind variable then actual query remains the same each time we gave call and Execution plan ready already so quick execution.

Where to Use Bind Variables : –
We can use Bind Variables in cases where we are generating the query depending up on the user inputs such as dropdownlist ,listbox selection ,logged in user name,value from Calendar control , Value entered in Textbox.Let me explain you.In the above examples when we are creating the query then only user input / logged in user getting changed but rest of the query remains the same so here we can use the bind variables using which we can avoid the query to get treated as new statement followed by the Parsing and hash generation.

eg. SELECT * FROM Tb_TestTable Where UserId = ‘ABC’ AND ValueByUser = ‘UserValue’
For the above statement for each user and for each selected value , Oracle created the seperate statment in Shared pool even for the same user but different selected values, which is also override to memory,we can use Bind variables here

SELECT * FROM Tb_TestTable Where UserId = :UserID AND ValueByUser = :UserValue

where UserID & UserValue are bindvariables begins with a single colon (“:”); this is how a bind variable is represented in a SQL statement.

How to Use Bind Variables in Asp.net We can use Bind Variables using ODP.NET and also Microsoft Application Block (Data Access block,using OracleHelper) To implement Bind Variables in Asp.net we can user Either OraCleHelper/OracleCommand class of Appliication blocks provided by Microsoft or using ODP.Net provided by Oracle

I am here going to use OracleHelper Class for my Sample Code for that we need use/reference two namespacesMicrosoft.ApplicationBlocks.Data & System.Data.OracleClient; Lets start with normal query where we need to apply bind variables here it is the case that we are having two dropdownlist on page and we are generating the query for filling the other Datacontrols like Repeater/Gridview and filling the datacontrol on index changed.

OracleConnection conn = new OracleConnection(Settings.ConnectionString);

conn.Open();

OracleCommand cmd = new OracleCommand();

cmd.Connection = conn;
string SqlQry = “SELECT * FROM TB_TABLE WHERE VALUE1 = “ValueFromFirstDropDownList” AND VALUE2 = “ValueFromSecondDropDownList”
cmd.CommandText = SqlQry;

DataSet ds = new DataSet();

OracleDataAdapter da = new OracleDataAdapter(cmd);

da.Fill(ds);
when each time the new values are selected in the dropdownlist Datacontrol needs to filled but when we selected the new values then query gets changed and each time Oracle treats it as new query although only Value1,value2 are changed so each time what oracle do is it goes through all the procedures like parsing,interpreting,Hash value creation.
we can avoid this by using Bind Variables here, so each time when we change the selected values then it will be get the same query the Shared Pool and the time & memeory override can be saved.
OracleConnection conn = new OracleConnection(Settings.ConnectionString);

conn.Open();

OracleCommand cmd = new OracleCommand();

cmd.Connection = conn;

cmd.Parameters.Add(new OracleParameter(“SelectedVALUE1”, dropdownlist1.SelectedValue));

cmd.Parameters.Add(new OracleParameter(“SelectedVALUE2”, dropdownlist2.SelectedValue));
string SqlQry = “SELECT * FROM TB_TABLE WHERE VALUE1 = :SelectedVALUE1 AND VALUE2 = :SelectedVALUE2”;
cmd.CommandText = SqlQry;

DataSet ds = new DataSet();

OracleDataAdapter da = new OracleDataAdapter(cmd);

da.Fill(ds);