2 Replies Latest reply: Apr 25, 2011 1:26 PM by kmacpher67 RSS

    simple update  fails ORA-01036: illegal variable name/number

    kmacpher67
      ORA-01036: illegal variable name/number

      I've been working on this one for a few days. It's killing me. Any help or sense of direction would be appreciated.
      I'm running Visual Studio Express and deploying to IIS2005 .NET 2.0 projects. The select statements work fine. The update GridView of list of rows, click on the edit button, click on update, then get this error. I've hardcoded the sql statement, it still fails.

              UpdateCommand='update "blueuser"."ARM_KEEP_APR2011"  set "SUB_ID"=1010017578,"AGNT_ID"=3333 WHERE "assign_id"=:ASSIGN_ID'        

               <UpdateParameters>
                  <asp:Parameter Name="ASSIGN_ID" />
              </UpdateParameters>
      I tried everything, so then I updated the default driver for Oracle on client side.

      .NET Framework Data Provider for Oracle
      SerVER: 10.2.0.3.0 Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
      With the Partitioning, OLAP and Data Mining options

      I updated the web.config. I tried updating the local project GAC, this is correctly pointing to the DLL HKLM\software\Microsoft\.NETFramework\<frameworkversion>\AssemblyFoldersEx\ODP.NET
      The default value should point to the location of Oracle.DataAccess.dll and I put a copy in the local project bin directory too.

      Last night before I left the office, this worked when Iclicked the update

           <system.data>
                <DbProviderFactories>
                     <remove invariant="Oracle.DataAccess.Client"/>
                     <add name="Oracle Data Provider for .NET" invariant="Oracle.DataAccess.Client" description="Oracle Data Provider for .NET" type="Oracle.DataAccess.Client.OracleClientFactory, Oracle.DataAccess, Version=2.102.2.20, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
                </DbProviderFactories>
           </system.data>

      Stack Trace: 


      [OracleException (0x80131938): ORA-01036: illegal variable name/number
      ]
         System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc) +304889
         System.Data.OracleClient.OracleParameterBinding.Bind(OciStatementHandle statementHandle, NativeBuffer parameterBuffer, OracleConnection connection, Boolean& mustRelease, SafeHandle& handleToBind) +905
         System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals) +912
         System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor) +431
         System.Data.OracleClient.OracleCommand.ExecuteNonQuery() +115
         System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +386
         System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +325
         System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +92
         System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +907
         System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +704
         System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +95
         System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
         System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +123
         System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
         System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +118
         System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +135
         System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
         System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
         System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +175
         System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

      Version Information: Microsoft .NET Framework Version:2.0.50727.3082; ASP.NET Version:2.0.50727.3082
        • 1. Re: simple update  fails ORA-01036: illegal variable name/number
          kmacpher67
          Asked and answered here:

          [Resolved - http://forums.oracle.com/forums/thread.jspa?messageID=2366331&#2366331
          http://forums.oracle.com/forums/thread.jspa?threadID=621965

          Update web.config

          1> manually edit  providerName="Oracle.DataAccess.Client"
          2> removed ;Unicode=True

          === so then I got a ton of different errors related to me trying a bunch of stupid stuff.  Run around and I'm back to the
          good news is that a hardcoded 

                  UpdateCommand="update blueuser.ARM_KEEP_APR2011  set SUB_ID=1010017578,AGNT_ID=2540 WHERE assign_id=29993004"       
          WORKS***

          === however if i try to use binds;  failure.

                  UpdateCommand="update blueuser.ARM_KEEP_APR2011  set SUB_ID=1010017578,AGNT_ID=2540 WHERE assign_id=:ASSIGN_ID"            

                      <UpdateParameters>
                      <asp:Parameter Name="ASSIGN_ID" />
                  </UpdateParameters>

          [PRE]
          [OracleException (0x80004005): ORA-01036: illegal variable name/number]
          Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck) +1000
          Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck) +52
          Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery() +4293
          • 2. Re: simple update  fails ORA-01036: illegal variable name/number
            kmacpher67
            Well, it doesn't seem like anybody can answer this and it has to be done.
            I talked to some people this weekend, they said to bypass the Microsoft components when doing updates and inserts, write your own DAL layer classes in C#. So much for my new found girlfriend called "drag and drop" cause I feel like I've been dragged and dropped.