Comments

• bruce October 21, 2004 9:50 AM

  Thank you for the posting. It really helped, I have been trying to solve the same problem for 2 days without use.
  Yeah the error message should have been a little more descriptive :)
  

• bruce October 21, 2004 2:58 PM

  none of my parameters are close to be 32 characters long... What else could it be?

• bruce October 21, 2004 3:06 PM

  Another frequent cause for the error is not including a ':' prior to the parameter names added to the Parameters collection on the Command object.

• bruce October 27, 2004 5:14 PM

  I am getting this exception when I am trying to delete a row. Here is my sql statement:
  " DELETE FROM XXXX_TABLE WHERE UNIQUE_KEY = :UNIQUE_KEY"
  
  Everything works fine for the update and insert. Hummm!!!!

• bruce October 27, 2004 10:51 PM

  You have included the ':' at the front of the parameter names in the Parameters collection, right?
  
  Absent that, I'd be curious to see the code that is getting the statement executed. Feel free to mail it along.

• bruce October 28, 2004 11:11 AM

  We determined that the problem was in defining the parameter name. There happended to be a space at the end of the parameter name, "DELETEUNIQUEKEY " and when that space was removed, everything worked fine.
  
  Interesting how we turn gray or lose our hair over simple space issues.

• bruce November 24, 2004 1:46 PM

  Another cause for this error is failing to set the CommandType (oCmd.CommandType = CommandType.StoredProcedure;). After spending an hour trying everything from hard coding the values to renaming variables I looked at another working app I had written and found it. Variables/objects that are out of scope when you assign them to an OracleParameter can cause this error too.
  
  Happy coding ;)

• bruce December 15, 2004 5:33 PM

  I made a stupid mistake. I was defining it in the params collection as :facility_name but calling it as :facility in the commandtext... felt so dum after spending about 1/2 an hour :(

• bruce December 24, 2004 2:13 PM

  In Ascential DataStage, during coding Server Jobs, this stupid error comes when you havent included :parameter_name...thanks to this forum, I resolved this issue..
  
  Thx y'all

• bruce April 25, 2005 11:01 AM

  thanks for this.
  you saved me a great deal of trouble.
  

• bruce July 7, 2005 1:10 PM

  This also happens when building a dynamic SQL string and providing parameters which aren't called for in the SQL string.

• bruce July 22, 2005 7:57 AM

  thank you Mr. Hanson

• bruce July 28, 2005 7:19 PM

  Thanks Keith! I spent three days figuring out another programmer's code and your post led to the source of the problem. I could not think that having too many parameters defined and not using them in the INSERT/UPDATE statements would cause this error. Also, the error from Oracle didn't exactly lead towards this end.

• bruce August 12, 2005 9:32 AM

  Thanks Chris Hanson !
  for me that was it... so simple but such a problem !
  thanks again.

• bruce August 19, 2005 12:06 PM

  Any other causes? I'm defining my OracleCommand.CommandText as
  "select concat(EDSYSGRP_SYSTYPE, EDSYSGRP_SEQ) from EDSYS where CD = " & DBDataReader("dEDSYS_CD")
  
  Before this code, I've read a record and DBDataReader("dEDSYS_CD") contains "813". I can execute the SQL using Oracle SQL *Plus and it returns a value fine, but my ASP.NET program bombs with the "illegal variable name/number" error.
  Any suggestions?
  

• bruce August 19, 2005 12:27 PM

  Does the OracleCommand have any parameters defined on it? Given that there doesn't seem to be anything else in that simple statement that should cause the problem, it would be my first guess.

• bruce August 19, 2005 12:47 PM

  Hi I'm still having this problem and all the commands and adapters I'm using is automatically generated by the componet in Visual Studio. I totally got no idea why this is happening, once the data in the datagrid changes i call the update function of the dataadapter. Can anyone help me with this please coz I've been spending my whole afternoon sitting here trying to solve it.

• bruce August 19, 2005 5:36 PM

  If you're using generated commands, the problem could be that the parameters are prefaced with an '@' instead of a ':' as Oracle requried.

• bruce August 22, 2005 10:21 AM

  We are getting the same error for the one below trans. and 3 times on transaction ME22. There are no ABAP dumps for these errors.
  
  VA01 BY0 > ORA-01036: illegal variable name/number#
  VA01 BY2 Database error 1036 for

• bruce August 22, 2005 1:22 PM

  That was it, Bruce!! Thanks for your help. There was a parameter left sitting in the OracleCommand. Once I did a Parameters.Clear, it fixed the problem.
  
  Thanks again!!
  
  Roger

• bruce August 24, 2005 6:20 AM

  thanks Bruce, however I'm still havin the same error with the generated code. the Update function of the dataAdapter works fine for inserting data into the database but not for modifying or deleting data. I'm starting to think if this is because the primary key of the table I'm tryin to modify is a set of two columns rather than one, because I did the same thing for another table which contains a single primary key and everything works.

• bruce September 4, 2005 10:46 AM

  Thank you very much,Roger,clearing the Parameter collection of OracleCommmand solved my problem.

• bruce September 9, 2005 2:57 AM

  This error also occurs when you do an:
  
  insert blah (x,y,z) (?,? ?)
  
  Ie you forget a comma somewhere, which I often forget if putting newlines in between my ?'s
  

• bruce September 14, 2005 11:55 AM

  I am getting the same error for my SELECT statement. heres the fragment of the code
  
  ...."FROM OTC_TRADES OTC, TRADE_TQP_DNORMAL T, COUNTERPARTIES CPTY, LOCATION_CODES LC, COUNTRIES CPTY_COUNTRY, TAXES CPTY_TX, MGR.TBL_ENERGYTRADELISTFWD TLIST " +
  "WHERE OTC.OTC_TRADE_SEQ = T.OTC_TRADE_SEQ AND T.PAYMENT_DT = ?" + ....
  
  and the ADO.NET component frag is
  
  .
  .
  oCommand.CommandType = CommandType.Text;
  oCommand.CommandText = CreateQuery();
  oCommand.Parameters.Add("@payment_dt",OleDbType.Date).Value = dtpPaymentDate.Value;
  
  oDataAdapter.SelectCommand = oCommand;
  .
  .
  Could someone help please.

• bruce September 14, 2005 12:07 PM

  Assuming you're going against an Oracle database, I'm betting that the problem is the '?' at the end of your SELECT statement fragment. In Oracle, the format for a parameter is :parmname. So in your SELECT, it should be
  
  T.PAYMENT_DT = :PAYMENT_DT.
  
  Also, this name is used when you Add to the Parameters collection. So your Add statement should be
  
  oCommand.Parameters.Add(":PAYMENT_DT",OleDbType.Date).Value = dtpPaymentDate.Value;
  
  Hope that helps.

• bruce October 4, 2005 10:53 AM

  Many thanks Bruce, saved me a lot of hussle! For some reason, the example for Oracle in MSDN contains the '@' mark before the parameter name. I replaced it with ':' and now it works! Shana Tova.

• bruce October 16, 2005 12:12 PM

  Thanks guys, this discussion was very helpful solving some problems I had for 2 days !

• bruce October 17, 2005 8:49 AM

  Thank Bruce!!!, I passed a twin days with this problem... in my case was:
  
  [quote]If you're using generated commands, the problem could be that the parameters are prefaced with an '@' instead of a ':' as Oracle requried. [/quote]
  
  
  Thank a lot ^.^v

• bruce November 3, 2005 11:45 AM

  Thanks guys.
  Clearing the parameters collection was my problem!
  

• bruce November 8, 2005 7:11 AM

  dfdf df df df dfdf

• bruce November 8, 2005 2:28 PM

  In an OCI appl for sql statement
  text* sqlstmt = (text *)("Insert Into %s.%s (CO, DIV1, BRH, CUST, WH, PARTNO, YM, DT, SALTQTY, SALTaMT, SALTSC, SALTGL, OINTQTY, OINTAMT, OINTSC, OINTGL, LOGID) Values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17)", szUserName, szTableName);
  
  I am trying to bind structure variable using following code
  if (OCIBindByPos(stmt, &bnd[0], err, (ub4) 1,
  (dvoid *) str.szCo, (sb4) sizeof(str.szCo), SQLT_AFC,(dvoid *) 0, (ub2 *)0, (ub2 *)0,
  (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
  || OCIBindByPos(stmt, &bnd[1], err, (ub4) 2,
  (dvoid *) str.szDiv1, (sb4) sizeof(str.szDiv1), SQLT_AFC,(dvoid *) 0, (ub2 *)0, (ub2 *)0,(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
  || OCIBindByPos(stmt, &bnd[2], err, (ub4) 3,
  (dvoid *) str.szBrh, (sb4) sizeof(str.szBrh), SQLT_AFC,(dvoid *) 0, (ub2 *)0, (ub2 *)0,
  (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
  || OCIBindByPos(stmt, &bnd[3], err, (ub4) 4,
  (dvoid *) str.szCust, (sb4) sizeof(str.szCust), SQLT_AFC,(dvoid *) 0, (ub2 *)0, (ub2 *)0,(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
  || OCIBindByPos(stmt, &bnd[4], err, (ub4) 5,
  (dvoid *) str.szWh, (sb4) sizeof(str.szWh), SQLT_AFC,(dvoid *) 0, (ub2 *)0, (ub2 *)0,
  (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
  || OCIBindByPos(stmt, &bnd[5], err, (ub4) 6,
  (dvoid *) str.szPartno, (sb4) sizeof(str.szPartno), SQLT_AFC,(dvoid *) 0, (ub2 *)0, (ub2 *)0,(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
  || OCIBindByPos(stmt, &bnd[6], err, (ub4) 7,
  (dvoid *) str.szYm, (sb4) sizeof(str.szYm), SQLT_AFC, (dvoid *) 0, (ub2 *)0, (ub2 *)0,
  (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
  || OCIBindByPos(stmt, &bnd[7], err, (ub4) 8,
  (dvoid *) str.szDt, (sb4) sizeof(str.szDt), SQLT_AFC,(dvoid *) 0, (ub2 *)0, (ub2 *)0,
  (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
  || OCIBindByPos(stmt, &bnd[8], err, (ub4) 9,
  (dvoid *) str.szSaltqty, (sb4) sizeof(str.szSaltqty), SQLT_NUM,(dvoid *) 0, (ub2 *)0, (ub2 *)0,(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
  || OCIBindByPos(stmt, &bnd[9], err, (ub4) 10,
  (dvoid *) str.szSaltamt, (sb4) sizeof(str.szSaltamt), SQLT_NUM,(dvoid *) 0, (ub2 *)0, (ub2 *)0,(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
  || OCIBindByPos(stmt, &bnd[10], err, (ub4) 11,
  (dvoid *) str.szSaltsc, (sb4) sizeof(str.szSaltsc), SQLT_NUM,(dvoid *) 0, (ub2 *)0, (ub2 *)0,(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
  || OCIBindByPos(stmt, &bnd[11], err, (ub4) 12,
  (dvoid *) str.szSaltgl, (sb4) sizeof(str.szSaltgl), SQLT_NUM,(dvoid *) 0, (ub2 *)0, (ub2 *)0,(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
  || OCIBindByPos(stmt, &bnd[12], err, (ub4) 13,
  (dvoid *) str.szOintqty, (sb4) sizeof(str.szOintqty), SQLT_NUM,(dvoid *) 0, (ub2 *)0, (ub2 *)0,(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
  || OCIBindByPos(stmt, &bnd[13], err, (ub4) 14,
  (dvoid *) str.szOintamt, (sb4) sizeof(str.szOintamt), SQLT_NUM,(dvoid *) 0, (ub2 *)0, (ub2 *)0,(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
  || OCIBindByPos(stmt, &bnd[14], err, (ub4) 15,
  (dvoid *) str.szOintsc, (sb4) sizeof(str.szOintsc), SQLT_NUM,(dvoid *) 0, (ub2 *)0, (ub2 *)0,(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
  || OCIBindByPos(stmt, &bnd[15], err, (ub4) 16,
  (dvoid *) str.szOintgl, (sb4) sizeof(str.szOintgl), SQLT_NUM,(dvoid *) 0, (ub2 *)0, (ub2 *)0,(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
  || OCIBindByPos(stmt, &bnd[16], err, (ub4) 17,
  (dvoid *) str.szLogid, (sb4) sizeof(str.szLogid), SQLT_NUM,(dvoid *) 0, (ub2 *)0, (ub2 *)0,(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT))
  {
  (void) printf("FAILED: OCIBindByPos()\n");
  report_error(err);
  return OCI_ERROR;
  }
  
  And I get "ORA-01036: illegal variable name/number" error...
  could you please help me.
  
  Thanks,
  Priyanka

• bruce December 1, 2005 3:02 AM

  I am still experiencing the problem!
  
  Someone please help, I am using the colon (:) before my parameter.
  
  Here is the code:
  ---------------------------
  
  <asp:SqlDataSource ID="OracleDataSourceCourseSearchResults" runat="server" ConnectionString="<%$ ConnectionStrings:OracleDatabaseConnectionString %>"
  ProviderName="<%$ ConnectionStrings:OracleDatabaseConnectionString.ProviderName %>"
  SelectCommand="SELECT CLASS.CALLNO, CLASS.COURSENO FROM class, course, prof where (class.courseno = course.courseno) and (prof.ssn = class.profssn) and (class.courseno like '%:CourseSubject%') order by CLASS.COURSENO"
  >
  <SelectParameters>
  <asp:ControlParameter ControlID="DropDownListCourseSubject" Name=":CourseSubject"
  Type="String" PropertyName="SelectedValue" ConvertEmptyStringToNull="False" />
  </SelectParameters>
  </asp:SqlDataSource>
  
  -----------------------------------
  
  I have tried Name=":CourseSubject" & Name="CourseSubject", neither work and I receive ORA-01036 error. When I remove the <SelectParameters> selection and replace :CourseSubject in the query string with a hardcoded value (e.g. 'isy300'), it works.
  
  Any ideas?
  
  Thanks in advance

• bruce December 5, 2005 9:52 AM

  Before divide with a number,just check whether it is a string or a numeric field.
  
  then dive it.
  
  
  Regards
  Sangram

• bruce December 6, 2005 11:50 AM

  Michael,
  
  The problem is that you can't use parameterize values as if it were inside just a variable. In other words, the parameter doesn't just be plugged into SELECT statement. To do what you're trying to accomplish, either the '%' symbols need to be attached to the parameter value before the method is called, or the SQL needs to be changed to a stored procedure and the SQL to be executed is dynamically built within the SP.
  
  Hope that helps.

• bruce December 15, 2005 3:32 PM

  Hi people,
  I hope im in time.
  
  Im having the same error, but just when we change our application from my computer to the server.
  When the app connect to the database from my computer everything works fine (im using ODP.NET 9.2.0.401) but when we test it on the server y get the infamous error ORA-01036 on the server they have ODP.NET 9.2.0.2102.
  
  Im really confused cause y tested my application thousend of times and y works fine but when finally i have to deploy this error comes.
  
  Im really desperate i have to deliver de application in two days or ill be ..
  
  here is the code of my class for connect and query he Oracle database:
  
  Public Class classQueryDataSet
  'Implements IDisposable
  Public Sub Dispose() 'Implements IDisposable.Dispose
  ' Dispose(True)
  'GC.SuppressFinalize(Me)
  da.Dispose()
  cmd.Parameters.Clear()
  cmd.Dispose()
  If conn.State = ConnectionState.Open Then conn.Close()
  'conn.Close()
  conn.Dispose()
  
  'conn.Dispose()
  
  End Sub
  
  Protected Overrides Sub Finalize()
  ' Dispose(True)
  'GC.SuppressFinalize(Me)
  da.Dispose()
  cmd.Parameters.Clear()
  cmd.Dispose()
  If conn.State = ConnectionState.Open Then conn.Close()
  'conn.Close()
  conn.Dispose()
  
  'conn.Dispose()
  ' Simply call Dispose(False).
  'cmd.Parameters.Clear()
  'cmd.Dispose()
  'conn.Close()
  'conn.Dispose()
  'If conn.State = ConnectionState.Open Then conn.Close()
  'conn.Dispose()
  MyBase.Finalize()
  'Dispose(False)
  End Sub
  
  
  ReadOnly CONN_STRING As String = ConfigurationSettings.AppSettings("strconnCATALOTECA")
  Private da As OracleDataAdapter
  Private cmd As OracleCommand
  Private conn As OracleConnection
  
  Public Sub New(ByVal nomProcAlm As String) 'Public Sub New(ByVal dbConn As OracleConnection, ByVal nomProcAlm As String)
  conn = New OracleConnection(CONN_STRING)
  conn.Open()
  cmd = New OracleCommand
  cmd.Connection = conn
  cmd.CommandType = CommandType.StoredProcedure
  cmd.CommandText = nomProcAlm
  'Parámetro de salida de Proc. Alm (REF CURSOR)
  cmd.Parameters.Add(":queryResults", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output)
  'Se crea el Data Adapter para llenar el Dataset
  da = New OracleDataAdapter(cmd)
  
  End Sub
  
  
  ' Protected Overrides Sub Finalize()
  ' Desctructor code to free unmanaged resources
  '' da.Dispose()
  ' cmd.Parameters.Clear()
  ' cmd.Dispose()
  ' conn.Close()
  ' If conn.State = ConnectionState.Open Then conn.Close()
  ' conn.Dispose()
  
  ' MyBase.Finalize()
  ' End Sub
  
  
  'Public Sub Dispose()
  ' da.Dispose()
  ' cmd.Parameters.Clear()
  ' cmd.Dispose()
  ' conn.Close()
  ' conn.Dispose()
  'End Sub
  Public Sub Dispose_Connection()
  conn.Close()
  conn.Dispose()
  If conn.State = ConnectionState.Open Then conn.Close()
  conn.Dispose()
  End Sub
  
  'Método para ejecutar el procedimieno almacenado
  Public Function execProcAlm(ByVal nombreTabla As String) As DataSet
  Dim ds As DataSet = New DataSet
  da.Fill(ds, nombreTabla)
  
  Return ds
  ds.Dispose()
  da.Dispose()
  End Function
  
  Public Function execProcAlm_NonQuery() As Integer
  Dim nroFilas As Integer = cmd.ExecuteNonQuery()
  Return nroFilas
  End Function
  Public Function execProcAlm_DataTable(ByVal nombreTabla As String) As DataTable
  Dim ds As DataSet = New DataSet
  da.Fill(ds, nombreTabla)
  
  Return ds.Tables(0)
  ds.Dispose()
  da.Dispose()
  
  End Function
  Public Sub addParam_String_ProcAlm(ByVal tipoParam As OracleDbType, ByVal valParam As String)
  Dim paramEntrada As OracleParameter = New OracleParameter
  paramEntrada.OracleDbType = tipoParam
  paramEntrada.Value = valParam
  paramEntrada.Direction = ParameterDirection.Input
  'Nombre de parametro
  paramEntrada.ParameterName = ":VI_VA_"
  'Se inserta en OracleCommand
  cmd.Parameters.Add(paramEntrada)
  
  End Sub
  Public Sub addParam_Integer_ProcAlm(ByVal tipoParam As OracleDbType, ByVal valParam As Integer)
  Dim paramEntrada As OracleParameter = New OracleParameter
  paramEntrada.OracleDbType = tipoParam
  paramEntrada.Value = valParam
  paramEntrada.Direction = ParameterDirection.Input
  
  'Nombre de parametro
  paramEntrada.ParameterName = ":VI_IN_"
  
  'Se inserta en OracleCommand
  cmd.Parameters.Add(paramEntrada)
  
  End Sub
  
  
  End Class
  

• bruce January 2, 2006 6:32 AM

  For me it was because I had put a space after : For example I had put : site. When I changed it to :site, it worked.
  
  

• bruce January 24, 2006 4:45 AM

  I m getting the above error statement while executing a .net application using stored procedure in oracle.i m using oracle as backend
  Pls help me

• bruce January 30, 2006 3:44 PM

  Thanks to you all. This discussion helped me out of a real jam. You guys rock!

• bruce February 8, 2006 4:27 PM

  'Any helpful hints when trying to call an Oracle Function instead of a Procedure and get this error?

• bruce March 13, 2006 2:52 AM

  hey Chris Hanson,
  Thanx for the timely help

• bruce March 16, 2006 12:39 PM

  Thanks all. Discussion helped me to resolve couple of issues and saved time.
  
  Good Job. Thanks.

• bruce March 19, 2006 3:33 PM

  Hi guys, I am still having the same problem, I have tried @ and : infront of the parameters, and still not working... please help.
  
  dataSource.InsertCommand = "insert into ASP_TEST (firstname,surname, birthday) values (:firstname, :surname,to_date(:bday,'DD/MM/YYYY'))";
  dataSource.InsertParameters.Add(":firstname", firstnameTextBox.Text.ToString());
  dataSource.InsertParameters.Add(":surname", surnameTextBox.Text.ToString());
  dataSource.InsertParameters.Add(":bday", birthdayTextBox.Text.ToString());
  try
  {
  dataSource.Insert();
  }
  catch (Exception ee)
  {
  Label1.Text = ee.Message.ToString();
  }

• bruce March 28, 2006 2:01 AM

  guys ,i am also getting the same error,I tried out many commments that were given,but didnt work out.getting the same error.
  
  'illegal name/value'

• bruce March 31, 2006 1:10 AM

  hi guys,
  
  this error occured when i'm trying to insert a new row. Which is the correct SQL statement correct?
  
  1.INSERT INTO mytable(age, name) VALUES(?,?)
  2.INSERT INTO mytable(age, name) VALUES(:1,:2)
  3.INSERT INTO mytable(age, name) VALUES(:age,:name)
  4.INSERT INTO mytable(age, name) VALUES(@age,@name)
  2.INSERT INTO mytable(age, name) VALUES(,)

• bruce April 3, 2006 6:50 AM

  Hi guys,
  
  please can anyone from help,When i m trying to cal a procedure which selects some data from the database.I m getting this error.

• bruce April 3, 2006 6:57 AM

  Hi guys,
  
  please can anyone from help,When i m trying to cal a procedure which selects some data from the database.I m getting this error.

• bruce April 3, 2006 7:36 AM

  .Hi guys,
  Please anyone can help me.From past 3 days i m struggling with this error.I am trying to call a procedure which retrieves some data from the database .But i m getting this error "Invalid variable name/number".

• bruce April 12, 2006 6:20 AM

  HI,
  I am facing this stupid problem in .Net application using oracle as a backend.When firing the insert query. At the cmd.ExcuteNonQuery(),thruoghs this exception.

• bruce April 19, 2006 3:10 AM

  Hi,
  I truly want to thank you. My error was even worse: I forgot to clear the parameter list between two db-insertions.
  For each one all the parameters were correct :)
  I found the solution thanks to one of you :)
  Again: thank you !

• bruce May 11, 2006 3:25 AM

  Hai Guys
  Plz help me that i was bothered with this error from the last 3 days. I wrote a Stored procedure in ORacle 9i. It's working fine there n number of times.
  But i call the same procedure in VB 6.0. For the first time it works successfully but from the 2nd time onwards it gives this error. Please help me
  this is procedure calling in VB
  
  Dim Param1 As New ADODB.Parameter
  Dim Param2 As New ADODB.Parameter
  
  Cmd.ActiveConnection = con
  
  Cmd.CommandType = adCmdStoredProc
  
  Set Param1 = Cmd.CreateParameter(":Param1", adVariant, adParamInput, 27, CStr("Edward"))
  Cmd.Parameters.Append Param1
  
  Set Param2 = Cmd.CreateParameter(":Param2", adVariant, adParamInput, 27, CStr("Diesel"))
  Cmd.Parameters.Append Param2
  
  Cmd.CommandText = "Mpurchase.CheckVendMat"
  Cmd.Execute
  MsgBox "Over"
  
  
  and this is the ORacle procedure
  CREATE OR REPLACE PROCEDURE Mpurchase.Checkvendmat(CVendid nVARCHAR2,CMatid nVARCHAR2) IS
  X NUMBER(2):=0;
  BEGIN
  SELECT COUNT(*) INTO X FROM Mpurchase.TMP_VENDSUPPLYMAT
  WHERE Vendid=Cvendid AND Matid=CMatid;
  IF X=0 THEN
  INSERT INTO Mpurchase.TMP_VENDSUPPLYMAT(Vendid,MatId) VALUES(Cvendid,CMatid);
  COMMIT;
  END IF;
  END;
  
  Plz help me
  
  
  

• bruce July 18, 2006 8:08 AM

  I'm getting this error using a gridview. Anyone have any insight on this? Thanks in advance for your help! Here's my code:
  
  <asp:SqlDataSource ID="dsWarr" runat="server" ConnectionString="<%$ ConnectionStrings:PROD1 %>" ProviderName="<%$ ConnectionStrings:PROD1.ProviderName %>"
  SelectCommand="SELECT PROJECT_ID, UNIT, WAR_MONS_SHIP, WAR_MONS_START, TO_CHAR(SHIP_DATE, 'MM/DD/YYYY') SHIP_DATE, SHIP_ACTUAL, TO_CHAR(STARTUP_DATE,'MM/DD/YYYY') STARTUP_DATE, STARTUP_ACTUAL, TO_CHAR(CREATED_DATE,'MM/DD/YYYY') CREATED_DATE, NOTES, CLOSED_DATE, CLOSED_STATUS FROM IFSAPP.TLT_PROJECT_MASTER"
  UpdateCommand="UPDATE IFSAPP.TLT_PROJECT_MASTER SET UNIT=:UNIT WHERE PROJECT_ID=:PROJECT_ID">
  <UpdateParameters>
  <asp:Parameter Name="UNIT" Type="String" />
  <asp:Parameter Name="PROJECT_ID" Type="String" />
  </UpdateParameters>
  </asp:SqlDataSource>
  <asp:GridView ID="gvWarr" DataKeyNames="PROJECT_ID" runat="server" AllowPaging="True" AllowSorting="True"
  DataSourceID="dsWarr" AutoGenerateEditButton="True" PageSize="50">
  </asp:GridView>

• bruce July 27, 2006 12:48 PM

  Just to add to the discussion, I encountered this very "descriptive" error message when trying to send the content of null variables that weren't string typed.
  
  In my scenario I had a DateTime and Double variables that weren't initialized but used to replace 2 parameters of the query.
  It took me a long time to realize that Oracle or the ADO.Net component for Oracle didn't like that.
  I had to test if the variables were initialized or null and replace by an explicit NULL in the query for those cases.
  
  Hope this helps. If anyone has a better solution for this scenario let me know. ;)

• bruce July 28, 2006 10:45 AM

  Thanks!!!! I had the same error with a select statement and a SelectParameters.Clear fixed my error.
  
  

• bruce August 9, 2006 12:58 PM

  I am getting the same error with my UPDATE commands. I read through the entire post above and I still can't seem to figure it out. Here is my code.
  
  protected void ReturnData()
  {
  string strReturnSQL = "UPDATE POSTALSERV_ADM.PACKAGE_ID" +
  "SET PKG_RETURN_DATE = :PKG_RETURN_DATE" +
  "WHERE PKG_ID=:PKG_ID";
  
  
  OracleConnection ocnn = Utilities.DBConnection;
  OracleCommand cmd = new OracleCommand(strReturnSQL, ocnn);
  
  cmd.Parameters.Add(new OracleParameter(":PKG_RETURN_DATE", Utilities.TestForNullDate(datDate)));
  cmd.Parameters.Add(new OracleParameter(":PKG_ID", Utilities.TestForNullStringValues(strPkgID)));
  
  cmd.ExecuteNonQuery();
  ocnn.Close();
  }

• bruce August 9, 2006 1:05 PM

  Mary, if you look closely at the SQL statement you built, there are some spaces missing. Specifically between PACKAGE_ID and SET and RETURN_DATE and WHERE.
  
  UPDATE POSTALSERV_ADM.PACKAGE_IDSET PKG_RETURN_DATE = :PKG_RETURN_DATEWHERE PKG_ID=:PKG_ID
  
  I suspect that adding the spaces will solve the problem.

• bruce August 9, 2006 1:13 PM

  Thanks Bruce. I did change those, and now I get a new error, but at least the old error is gone.

• bruce August 9, 2006 1:19 PM

  Bah, I fixed it. The spaces were a problem, and I fixed the new error as well. Turns out my PKG_ID was defined in TOAD as numeric, when it really should be a string.

• bruce August 9, 2006 1:58 PM

  Thanks for the article. i was trying to solve this issue for more than an hour and ur solution gave the answer.

• bruce August 30, 2006 5:31 PM

  Great resource for this issue. Thanks folks.

• bruce September 7, 2006 2:32 AM

  asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
  DeleteCommand="DELTE FROM QI_ISSUE WHERE (EMP_NO = :EMP_NO) AND (LOT_NO = :LOT_NO) AND (DETECTED_EMP_NO = :DETECTED_EMP_NO)"
  ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
  SelectCommand="SELECT EMP_NO, DATE_SELECTED, LOT_NO, STAGE_DETECTED, DETECTED_EMP_NAME, DETECTED_EMP_NO, NATURE_OF_ISSUE, ERROR_TYPE, EMP_NAME,SHIFT_WORK, ERROR_STAGE FROM QI_ISSUE WHERE (EMP_NO = :EMP_NO) AND (LOT_NO = :LOT_NO) AND (DETECTED_EMP_NO = :DETECTED_EMP_NO)">
  <DeleteParameters>
  <asp:Parameter Name="EMP_NO" />
  <asp:Parameter Name="EMP_NAME" />
  <asp:Parameter Name="SHIFT_WORK" />
  <asp:Parameter Name="DATE_SELECTED" />
  <asp:Parameter Name="LOT_NO" />
  <asp:Parameter Name="STAGE_DETECTED" />
  <asp:Parameter Name="DETECTED_EMP_NAME" />
  <asp:Parameter Name="DETECTED_EMP_NO" />
  <asp:Parameter Name="NATURE_OF_ISSUE" />
  <asp:Parameter Name="ERROR_TYPE" />
  <asp:Parameter Name="ERROR_STAGE" />
  </DeleteParameters>
  <SelectParameters>
  <asp:QueryStringParameter Name="EMP_NO" QueryStringField="ID" />
  <asp:QueryStringParameter Name="LOT_NO" QueryStringField="LOT" />
  <asp:QueryStringParameter Name="DETECTED_EMP_NO" QueryStringField="DETECTED" />
  </SelectParameters>
  </asp:SqlDataSource>

• bruce September 7, 2006 2:33 AM

  i got this error..can anyone help me..?

• bruce September 19, 2006 8:21 AM

  Thanks! My worked by writing cmd.Parameters.Delete("unused_parameters")
  I m re-using the same object for 2-4 procedure. So by deleting the unused parameters, the problem is solved

• bruce October 30, 2006 3:26 PM

  Another cause of this error is missing the "," between two parameters. Yet another simple typo/mistake causing a 1/2 hour of searching.
  
  Thank You

• bruce October 30, 2006 3:28 PM

  Another cause of this error is missing the "," between two parameters. Yet another simple typo/mistake causing a 1/2 hour of searching.
  
  (i.e. :creationdatetime, :status :description, )

• bruce November 8, 2006 11:05 AM

  Hi guys,
  I am using a stored procedure to insert values in to a table. I am working on c#.net and oracle as backend and i am using application blocks to deal with database operations and i got this ora -01030 illegal variable name/number error. I tried everything like shortening variable names and chekced the parameters names but still unable to fix it..can any one help

• bruce November 28, 2006 10:55 AM

  Just wanted to say thanks!! Great info in here saved me lots of time on my vb.net routine!

• bruce November 28, 2006 3:10 PM

  I'm still getting this error, i tried all the combinations.
  
  <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Con1 %>"
  ProviderName="<%$ ConnectionStrings:Con1.ProviderName %>" SelectCommand='SELECT "BUDGET_CTRL_CD", "BUDGET_YEAR_DT", "SCENARIO_NM", "VERSION_NBR", "BUDGET_CTRL_DT", "BUDGET_CTRL_DESC", "COMMENT_TXT" FROM "BUDGET_CONTROL" ORDER BY "BUDGET_CTRL_CD"'
  UpdateCommand="UPDATE BUDGET_CONTROL SET BUDGET_CTRL_DESC = :BUDGET_CTRL_DESC, COMMENT_TXT = :COMMENT_TXT WHERE (BUDGET_CTRL_CD = :BUDGET_CTRL_CD)">
  <UpdateParameters>
  <asp:Parameter Name=":BUDGET_CTRL_DESC" />
  <asp:Parameter Name=":COMMENT_TXT" />
  <asp:Parameter Name=":BUDGET_CTRL_CD" Type="Decimal" />
  </UpdateParameters>
  </asp:SqlDataSource>

• bruce November 29, 2006 8:57 AM

  I found the solution, this error also raises when you don't add all the columns in the update query.
  
  But this error message is too generalized, it doesn't say what is the exact problem.
  
  nyways, this blog helped me a lot.
  
  Thanks,
  Chintan

• Dee April 16, 2007 4:49 PM

  Thanks guys for your input. The cryptic message is a punishment for not doing this:

  cmd.CommandType = CommandType.StoredProcedure;

  works now !

• Marty May 2, 2007 8:31 AM

  I found this thread and was having the 1036 error using a SqlDataSource and GridView.

  I implemented the event OnDeleting and noticed in the command object it was using both both keys defined in the DataKeys of the gridview even though I only defined one of them for the delete command parameters.  I added it to the delete command and it worked.

  DELETE FROM RPT_SCHEDULE WHERE SCH_ID = :SCH_ID and RPT_ID = :RPT_ID

  <DeleteParameters>                                                   <asp:Parameter Name="SCH_ID" />                                                </DeleteParameters>

  DataKeyNames="RPT_ID,SCH_ID"

  Now it works.  Go figure.  What's the point of having a DeleteParameters if it doesn't even seem to use it?

• Phaneesh Gururaj May 9, 2007 11:43 AM

  Got my stuff working, All I had to do was replace '@' with ':'. Thanks to all (:-)

• Kanchana V June 11, 2007 10:08 AM

  Thanks everyone,

  I might have spent days on this one but for these messages. There was , missing in my code.

• Sukh June 15, 2007 2:05 PM

  Tried everything but not working can anyone help

  here is my code:

  Store proc:

  ///////

  create or replace PROCEDURE     Test

  (P1_in number, P2_out out number, P3_out out number )

  AS

  BEGIN

  rent_out:= 100;

  hpd_rent_out:= 200;

  end;

  /////

  C# code:

  ORACON = new OracleConnection();

                 ORACON.ConnectionString = ConfigurationManager.AppSettings[STRCONNECTIONKEY];

  ORACOM = new OracleCommand("Test", ORACON);

  ORACOM.CommandType = CommandType.StoredProcedure;

  ORACOM.Parameters.Add("@P1_in", OracleType.Int32);

  ORACOM.Parameters[0].Value = 26603;

  ORACOM.Parameters.Add("@P2_out", OracleType.Int32);

  ORACOM.Parameters["@P2_out"].Direction = ParameterDirection.Output;

  ORACOM.Parameters.Add("@P3_out", OracleType.Int32);

  ORACOM.Parameters["@P3_out"].Direction = ParameterDirection.Output;

  ORACON.Open();

  OracleDataReader dr;

  dr = ORACOM.ExecuteReader();

  ////

  Thanks in advance.

• Jeff June 21, 2007 12:02 PM

  getting 01036 when attempting insert from detailsview. Finally took out paraemeters and hard-coded as follows:

  InsertCommand="INSERT INTO FNAREA(FNAREAID, FNAREADESC, SUSPND, CREATEDBY, RECDATE) VALUES (FNAREA_ID_SEQ.NEXTVAL, 'XX', 'N', 'JF1111', TO_DATE('01/01/07', 'mm/dd/yy'))"

  This exact insert statement runs fine via sqlplus but still gives me 01036 from details in .net? Help!

• Zewb June 29, 2007 1:17 PM

  Hoping someone will see something I'm not...I have two updates which I'm attempting.  One works fine the other is throwing the dreaded ORA-01036 error.  I'm using these from within a gridview, the first update with parameter set works fine, the second however does not.  The only thing I can think of is that maybe the parameters in the form fields are doing it???

  "UPDATE LOG SET ENTRY_TYPE = 'D', MODIFIED_BY = :Dispatcher WHERE (LOG_NUMBER = :LOG_NUMBER)"

  <asp:Parameter Name="LOG_NUMBER" />

  <asp:SessionParameter Name="DISPATCHER" SessionField="Dispatcher" />

  "UPDATE LOG SET MODIFIED_BY = :Dispatcher, LOCATION = :Location, ENTRY_LOG1 = :txtLogEntry, ENTRY_TYPE = 'E' WHERE (LOG_NUMBER = :LOG_NUMBER)"

  <asp:SessionParameter Name="DISPATCHER" SessionField="Dispatcher" />

  <asp:FormParameter FormField="Location" Name="LOCATION" />

  <asp:FormParameter FormField="txtEntryLog" Name="ENTRY_LOG1" />

  <asp:Parameter Name="LOG_NUMBER" />

• CHARLENE July 18, 2007 9:45 AM

  hi, I have this error when using the following sqldatasource with oracle 9.0, do you see anything wrong? thanks

  <asp:SqlDataSource ID="SqlDataSource1"

      Runat="server"

  selectCommand='SELECT "MAIN_ID", "CARRIER", "TRANS_AMT", "AREA", "TYPE", "SEVERITY", "REASON", "RJ", "REPAIR_NUM", "VISIT_NUM" FROM "ADJUDICATION_MAIN"'

      ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>"

                                 ProviderName="<%$ ConnectionStrings:ConnectionString2.ProviderName %>" UpdateCommand='update adjudication_main set carrier=:CARRIER where main_id=:MAIN_ID'

                                >

           <UpdateParameters>

             <asp:Parameter Name=":CARRIER" Type="String" DefaultValue="999"  />

          </UpdateParameters>

  </asp:SqlDataSource>

• bhagwan August 3, 2007 2:45 AM

  i am getting the exceptio ORA-01036: illegal variable name/number

  for the below code..

  objDataAccess.Connection_Open(strChannel)

             With objCommand

                 .Connection = objDataAccess.m_oraConnection

                 .CommandText = "EO_PKG_ADM.eo_pr_load_all_queues"

                 .CommandType = CommandType.StoredProcedure

                 .Parameters.Add(New OracleParameter("p_page_index", OracleType.VarChar, 6)).Direction = ParameterDirection.Input

                 .Parameters.Add(New OracleParameter("p_rows_per_page", OracleType.VarChar, 6)).Direction = ParameterDirection.Input

                 .Parameters.Add(New OracleParameter("p_order_by", OracleType.VarChar, 20)).Direction = ParameterDirection.Input

                 .Parameters.Add(New OracleParameter("p_re_count", OracleType.VarChar, 1)).Direction = ParameterDirection.Input

                 .Parameters.Add(New OracleParameter("p_count", OracleType.VarChar, 5)).Direction = ParameterDirection.Output

                 .Parameters.Add(New OracleParameter("p_queue_data ", OracleType.Cursor)).Direction = ParameterDirection.Output

                 .Parameters(0).Value = "0" ' gvAllQueues.PageIndex

                 .Parameters(1).Value = "10" 'gvAllQueues.PageSize

                 .Parameters(2).Value = "ECF_FOLDER_NAME" 'modifiedorderby

                 .Parameters(3).Value = "Y"

  at the statement- objAdapter.Fill(ds)

  generates the exception ORA-01036: illegal variable name/number

  can anyone help please.. ?

• Dan August 14, 2007 10:10 AM

  A little late to the dance; however, nonetheless 100% helpful.  My first (and hopefully last) time using Oracle.  Programming efficiency related, I cannot understand the debate between enterprise dbms.  SQL Server hands down is much easier to program against.  Thanks again... -Dan

• shanmugam August 25, 2007 7:30 AM

  i tried all this things but not working , i don't know where is error pls tell me here is my coding

  stored procedure:

  create or replace procedure Myemployeeproce(names varchar2, companys  out varchar2 )

  IS

  begin

  select company into companys FROM ifsapp.myemployee where name = names;

  end;

  vb.net :

   Dim cmd As OracleCommand = New OracleCommand("ifsapp.Myemployeeproce", con)

             cmd.Parameters.Add("names", OracleType.VarChar, 20).Value = "rajes"

             cmd.Parameters.Add("companys", OracleType.VarChar, 20).Direction = ParameterDirection.Output

             cmd.ExecuteNonQuery()

• bruce August 26, 2007 9:52 PM

  Shanmugam, try adding colons in front of the parameter names.

  cmd.Parameters.Add(":names", OracleType.VarChar, 20).Value = "rajes"

• Promod November 2, 2007 8:54 AM

  I was getting the same error and the reason was I was adding the comment in the sql with "-- bla bla" but when changed to "/*bla bla*/" the error gone.

• eldrian J November 15, 2007 11:36 PM

  Many thanks: the ole space-at-end-of-param name trick!

• Daniel M December 3, 2007 2:09 PM

  Also check the parameter names in the SQL statement and the ones in the Parameters collection of the command, they should be identical.

• Prashant February 5, 2008 3:13 AM

  re: Illegal Variable Name/Number in Oracle

  I am finding this error while generating expalin plan for a quiery. If I use ':' for bind variable in query then it shows this error and same query is run with hardcoded value then it works fine.....I am running it in Toad..so it there any toad setting affecting it ?

• saurabh April 2, 2008 5:37 AM

  I hv d same prob that u hv alrdy mentioned. This error throws up in Ascential DataStage in a server job and am using a parametre::

  SELECT

  LKP_DD_015_03_S_PROD_INT.T1_NAME,        LKP_DD_015_03_S_PROD_INT.T1_VENDR_OU_ID, LKP_DD_015_03_S_PROD_INT.T1_DESC_TEXT,   LKP_DD_015_03_S_PROD_INT.T1_ROW_ID

  FROM

  STAGING.LKP_DD_015_03_S_PROD_INT LKP_DD_015_03_S_PROD_INT

  WHERE         LKP_DD_015_03_S_PROD_INT.T1_ROW_ID = :1

  can you plase help where m i going wrong.?

• bruce April 4, 2008 8:48 AM

  saurabh, I don't believe that the parameter name in Oracle can be numeric. So instead of 'T1_ROW_ID = :1', you would need to set it to be 'T1_ROW_ID = :ROWID'

• Joel May 15, 2008 2:47 PM

  Thanks so much for this post, it saved me a lot of time!

• Phil June 26, 2008 5:40 PM

  Thanks guys.

  My problem turned out to be leaving quotation marks in the sql string.  ("Insert into tblCustomer(customerName) values (':CustomerName');".

  As soon as I removed the single quotes around :CustomerName, it worked fine.

  Cheers

• Bruce Johnson's SOA(P) Box July 28, 2008 9:01 AM

  I have recently had the opportunity to work ( once again ) with Oracle. Specifically, I had to create

• Sudhir July 28, 2008 11:56 PM

  Guys!

  This error, is got to do the parameter names that you pass, there is something wrong in it.

  -May be the length which should be limited to 31 chars.

  -The '@' before the name is not allowed in oracle.

  -The names are different.

  -All the parameters are not supplied.

  You can think of more.

• Rupesh Kumar October 30, 2008 12:20 AM

  I am using vs 2005 and oracle 8i for an web app, updation of data from gridview works fine when i write the select & update command in the design mode but throws this exception when i code all the above in code behind. Can anyone HELP?

• Sam March 12, 2009 8:28 AM

  So my problem was not clearing the paramters between multiple inserts during a transaction.

  My code was something like this:

  TransactionCommand.CommandText = commandText;

  TransactionCommand.Parameters.AddRange(parameters.ToArray());

  TransactionCommand.ExecuteNonQuery();

  At fist I kept clearing my parameter array, but not clearing the parameters from the command.

  So I changed it to:

  TransactionCommand.CommandText = commandText;

  TransactionCommand.Parameters.Clear();

  TransactionCommand.Parameters.AddRange(parameters.ToArray());

  TransactionCommand.ExecuteNonQuery();

  I hope this helps someone as this blog has helped me many times before.

Leave a Comment