8 Replies Latest reply on Aug 24, 2012 5:34 PM by EdStevens

    inconsistent datatypes: expected DATE got NUMBERORA-06512

    954056
      Hi,

      Sporadically getting this exception.I am using .Net 2.0 with ODP .net 2.112.1.0 . Oracle version is 11g . I have used BindByName = true.

      declare
      bv_EFFECTIVEFROM date := to_date('2008/05/30 02:04:21', 'YYYY/MM/DD HH:MI:SS');
      bv_EFFECTIVEFROM_c141 date := to_date('2008/05/30 02:04:32', 'YYYY/MM/DD HH:MI:SS');
      bv_EFFECTIVEFROM_c22 date := to_date('2008/05/30 02:04:20', 'YYYY/MM/DD HH:MI:SS');
      bv_EFFECTIVEFROM_c33 date := to_date('2008/05/30 02:04:28', 'YYYY/MM/DD HH:MI:SS');
      bv_EFFECTIVEFROM_c36 date := to_date('2008/05/30 02:04:29', 'YYYY/MM/DD HH:MI:SS');
      bv_EFFECTIVEFROM_c48 date := to_date('2008/05/30 02:04:30', 'YYYY/MM/DD HH:MI:SS');
      bv_EFFECTIVEFROM_c50 date := to_date('2008/05/30 02:04:31', 'YYYY/MM/DD HH:MI:SS');
      bv_EFFECTIVEFROM_c56 date := to_date('2008/05/30 02:04:22', 'YYYY/MM/DD HH:MI:SS');
      bv_EFFECTIVETO date := null;
      bv_ENTITYTYPE number := 1;
      bv_FileNameID number := 153;
      bv_MarketDateDeleted date := null;
      bv_MarketDateModified date := to_date('2012/07/27 12:00:00', 'YYYY/MM/DD HH:MI:SS');
      bv_MarketDateVerified date := to_date('2012/07/27 12:00:00', 'YYYY/MM/DD HH:MI:SS');
      bv_MASTERFILESETFILEID number := 1;
      bv_MASTERFILESETFILEID_c187 number := 0;
      bv_RELATEDFROMENTITYID number := 4295903973;
      bv_RELATEDFROMENTITYTYPE varchar2(12) := 'Organization';
      bv_RELATEDTOENTITYID number := 8590944794;
      bv_RELATEDTOENTITYID_c100 number := 8590945304;
      bv_RELATEDTOENTITYID_c101 number := 8590945305;
      bv_RELATEDTOENTITYID_c102 number := 8590945312;
      bv_RELATEDTOENTITYID_c103 number := 8590945316;
      bv_RELATEDTOENTITYID_c104 number := 8590945318;
      bv_RELATEDTOENTITYID_c105 number := 8590945320;
      bv_RELATEDTOENTITYID_c106 number := 8590945337;
      bv_RELATEDTOENTITYID_c107 number := 8590945347;
      bv_RELATEDTOENTITYID_c108 number := 8590945350;
      bv_RELATEDTOENTITYID_c109 number := 8590945357;
      bv_RELATEDTOENTITYID_c110 number := 8590945359;
      bv_RELATEDTOENTITYID_c23 number := 8590944822;
      bv_RELATEDTOENTITYID_c24 number := 8590944824;
      bv_RELATEDTOENTITYID_c25 number := 8590944876;
      bv_RELATEDTOENTITYID_c26 number := 8590944881;
      bv_RELATEDTOENTITYID_c27 number := 8590944954;
      bv_RELATEDTOENTITYID_c28 number := 8590944956;
      bv_RELATEDTOENTITYID_c29 number := 8590944960;
      bv_RELATEDTOENTITYID_c30 number := 8590944964;
      bv_RELATEDTOENTITYID_c31 number := 8590944968;
      bv_RELATEDTOENTITYID_c32 number := 8590944973;
      bv_RELATEDTOENTITYTYPE varchar2(10) := 'Instrument';
      bv_RELATIONSHIPTYPECODE varchar2(10) := 'IsIssuerOf';
      bv_SOURCE number := 3;
      bv_SOURCEENTITYID nvarchar2(10) := '4295903973';
      bv_StatusCode number := 96;
      bv_StatusLockKey nvarchar2(1) := null;
      begin
      begin
      begin
      begin
      begin
      insert into oa.organization_isissuerof (effectivefrom, effectiveto, entitytype, masterfilesetfileid, relatedfromentityid, relatedfromentitytype, relatedtoentityid, relatedtoentitytype, relationshiptypecode, source, sourceentityid) values (bv_effectivefrom, bv_effectiveto, bv_entitytype, bv_masterfilesetfileid, bv_relatedfromentityid, bv_relatedfromentitytype, bv_relatedtoentityid, bv_relatedtoentitytype, bv_relationshiptypecode, bv_source, bv_sourceentityid) ;
      end;
      end;
      begin
      update fpf.ascollectedsourceentityids set filenameid = bv_filenameid , marketdatedeleted = bv_marketdatedeleted , marketdatemodified = bv_marketdatemodified , marketdateverified = bv_marketdateverified , statuscode = bv_statuscode , statusdate = sysdate , statuslockkey = bv_statuslockkey where source = bv_source and entitytype = bv_entitytype and masterfilesetfileid = bv_masterfilesetfileid_c187 and sourceentityid = bv_sourceentityid;
      end;
      commit work;
      exception when others then begin
      rollback;
      raise;
      end;
      end;
      end;
      end;

      Regards,
      Rakesh
        • 1. Re: inconsistent datatypes: expected DATE got NUMBERORA-06512
          Khayyam
          This means you expect date format value but getting number format value.
          Convert it by using to_date function or give us detailed info about error (In which line happened) and we will correct it for you...
          • 2. Re: inconsistent datatypes: expected DATE got NUMBERORA-06512
            APC
            So what is your question? You've posted at lot of code which to the naked eye looks alright. But it doesn't seem to tie in with your reference to ODP.net.

            But then you use the dread word "sporadically". So it's probably a problem with the data you're passing. That is, it seems unlikely you're inserting multiple records with identical data so there must be something which replaces - or produces - those hard-coded strings.

            In short, a classic bad question. Lots of meaningless code, not nearly enough detail or context for us to be able to help you.

            Cheers, APC
            • 3. Re: inconsistent datatypes: expected DATE got NUMBERORA-06512
              954056
              Hi,

              please find the exception.

              Exception: Exception: ORA-00932: inconsistent datatypes: expected DATE got NUMBERORA-06512: at line 1StackTrace: at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck) at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck) at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery() at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteNonQuery(DbCommand command) at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DbCommand command) at ThomsonReuters.Database.Access.CommandManager.ExecuteNonQuery(DbCommand dbCommand) in D:\ICW\Sprint6\Src\ICW ESP Source Code\Database\Access\CommandManager.cs:

              This exception is getting sporadically only. I have loaded a file containing 300000 records. The data loading is buffer by buffer. But very few buffers got failed with this kind of exception.The earlier code snapshot is from a failing buffer. It seems alright from the dynamic sql.

              I have seen some other helps related to this and reconfirmed that BindByName = true is set.

              Kindly let me know if I need to share any more information.

              Code snippet.
              -------------------------------------------
              CommandManager cmdMgr = new CommandManager(_dbMgr);

              // we can use LastKeyInDataBuffer because there is only 1 entity per buffer
              DbCommand dbCmd = GenerateDML(entityDataBuffer.EntityData
              , entityDataBuffer.EntityStatus
              , ThreadItem.DataBufferKey.ToString()
              , entityDataBuffer.AsCollectedMetaData
              , entityDataBuffer.TransMetaData
              , entityDataBuffer.AsCollectedSourceEntityIDMapping
              , entityDataBuffer.CurrentEntityType);
              cmdMgr.AddDbCommand(dbCmd);

              DateTime start = DateTime.Now;
              _eventMgr.Trace(start
              , string.Format("ThreadPool: {0} ExecDML", _threadPool.PoolName)
              , TraceLevelEnum.High);

              // do not process empty transactions
              if (!cmdMgr.IsNullDbCommand)
              {
              cmdMgr.ExecuteNonQuery();
              //_eventMgr.Trace(string.Format("DEBUG Info [DML] EntityID: {0}, {1} {2}", ThreadItem.DataBufferKey.ToString(), Environment.NewLine, cmdMgr.CompoundCommandTextAsDebugScript), TraceLevelEnum.Low);
              }

              ------------------------------------------
              Thanks,
              Rakesh
              • 4. Re: inconsistent datatypes: expected DATE got NUMBERORA-06512
                sb92075
                951053 wrote:
                Hi,

                please find the exception.

                Exception: Exception: ORA-00932: inconsistent datatypes: expected DATE got NUMBERORA-06512: at line 1StackTrace: at
                You have a data dependent bug.
                So either eliminate the bad data or fix the code so it does not throw error.

                Since we do not have your tables or data, we can not run, test or improve posted code.
                • 5. Re: inconsistent datatypes: expected DATE got NUMBERORA-06512
                  APC
                  Some of your 300000 records contaiin data which doesn't match your data model. It's up to you to isolate those records and handle them somehow - fix the data, discard the records, whatever.

                  So you need to build some form of logging and error handling into your code. This is the price you pay for rolling your code instead of using the built-in functionality which Oracle offers. I suggest you consider using either SQL*Loader or External Tables to load your data (depending on how you want to call the load process). Both approaches provide a framework for handling bad records, and varying degrees of data cleansing. http://docs.oracle.com/cd/B19306_01/server.102/b14215/toc.htm.

                  Cheers, APC
                  • 6. Re: inconsistent datatypes: expected DATE got NUMBERORA-06512
                    954056
                    Sorry for the delayed reply. The issue was not because of the data problem. The sql size was exceeding the limit and the application throw the exception.I have reduced the buffer size and the issue got resolved by its own.

                    But the problem here is the exception message is not appropriate one. It throws 'inconsistent datatypes: expected DATE got NUMBERORA-06512'.

                    Thanks,
                    Rakesh
                    • 7. Re: inconsistent datatypes: expected DATE got NUMBERORA-06512
                      sb92075
                      951053 wrote:
                      Sorry for the delayed reply. The issue was not because of the data problem. The sql size was exceeding the limit and the application throw the exception.I have reduced the buffer size and the issue got resolved by its own.

                      But the problem here is the exception message is not appropriate one. It throws 'inconsistent datatypes: expected DATE got NUMBERORA-06512'.

                      Thanks,
                      Rakesh
                      above results from implicit datatype conversion.
                      change the data or do manual conversion correctly.
                      • 8. Re: inconsistent datatypes: expected DATE got NUMBERORA-06512
                        EdStevens
                        951053 wrote:
                        Sorry for the delayed reply. The issue was not because of the data problem. The sql size was exceeding the limit and the application throw the exception.I have reduced the buffer size and the issue got resolved by its own.

                        But the problem here is the exception message is not appropriate one. It throws 'inconsistent datatypes: expected DATE got NUMBERORA-06512'.

                        Thanks,
                        Rakesh
                        So you are left with two possible conclusions:

                        1) Oracle has a bug that causes it to mis-report an error

                        or

                        2) You mis-understood the root cause and so mis-intrepreted how your solution actually resulted in a fix.


                        Which will you put your money on?