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.

About these ads

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

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

  2. I’m often searching for brandnew posts in the WWW about this matter. Thx.

  3. Thanks for this post. What ended up fixing my ORA-01008 error was to add the parameters in the order to which they appeared in my SQL statement. In my case, this meant duplicating a parameter that was used twice but not sequentially.

  4. i created a simple table with the following code in sql, using oracle database 10g express edition—-
    create table hi(iname varchar2(5), state varchar2(5));
    the result shows table created. But when i try to insert the values using the code—
    insert into hi values(&iname, &state);
    it shows ORA-01008: not all variables bound
    plzz help me out with this..

    • collecteddotnet

      Hi ,
      Don’t use & , use :
      It should be insert into tab_xyz values(:x1_col1, :x2_col2);,
      Also check if you have bind variable declared with values before binding them.

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