none
Oracle Database Issue :inconsistent datatypes: expected DATE got NUMBER

    Question

  • Hi ,

    I am inserting the data into oracle database through WCF-Custom adapter (OracleDBbinding)

    I am getting following error

    Error details: Microsoft.ServiceModel.Channels.Common.TargetSystemException: ORA-06550: line 2, column 2045:
    PL/SQL: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
    ORA-06550: line 2, column 1:
    PL/SQL: SQL Statement ignored ---> Oracle.DataAccess.Client.OracleException: ORA-06550: line 2, column 2045:
    PL/SQL: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
    ORA-06550: line 2, column 1:
    PL/SQL: SQL Statement ignored

    the data type is date in schema and i am passing below values 1999-05-31 or 05-jul-12

    the database is storing the date format is 05-jul-12

    Wednesday, July 11, 2012 4:10 PM

All replies

  • I would suggest install the hotfix for BizTalk Oracle adapter. http://seroter.wordpress.com/2007/08/27/important-hotfixes-for-the-biztalk-oracle-adapter/

    Thanks, Raja MCTS BizTalk Server 2010, MCC If this answers your question please mark it accordingly

    Wednesday, July 11, 2012 5:23 PM
  • This hotfix is only avaliable for 32 bit but my system is 64 bit
    Thursday, July 12, 2012 7:08 AM
  • Try to install 32 bit hotfix on your 64 bit machine and then follow below steps.

    1. Click Start, click Run, type regedit, and then click OK.
    2. Locate the appropriate registry key, depending on the system:
      • On a 32-bit server, locate the following registry key:

    HKEY_LOCAL_MACHINE\Software\ Microsoft\BizTalkAdapters\Config\Global\Adapters\OracleDB

      • On a 64-bit server, locate the following registry key:

    HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\BizTalkAdapters\Config\Global\Adapters\OracleDB

    1. Right-click the registry key that you located in step 2, point to New, and then click DWORD Value.
    2. Type LocalTimes, and then press ENTER.
    3. Right-click LocalTimes, and then click Modify.
    4. In the Value data box, type 0, and then click OK. Note If you type a value other than 0, BizTalk Adapter for Oracle Database modifies the data in the DateTime field.
    5. Exit Registry Editor.

    Reference : http://support.microsoft.com/kb/921999


    Thanks, Raja MCTS BizTalk Server 2010, MCC If this answers your question please mark it accordingly

    Thursday, July 12, 2012 7:45 AM
  • I am able to update the data but only getting error in inserting the date
    Thursday, July 12, 2012 9:17 AM
  • Did you try that hotfix?

    Thanks, Raja MCTS BizTalk Server 2010, MCC If this answers your question please mark it accordingly

    Thursday, July 12, 2012 9:26 AM
  • In insert schema we have columns_names and query fields are there.

    I am not passing this values  previously and now i am passing  columns_names  but i am getting

    same error.

    what value we have to pass query .

    I think hotfix not required because if any problem is there update also not perform its updating .

    I have ran but i am getting LOB adapters not found error

    Thursday, July 12, 2012 11:26 AM
  •  what is the source?(flatfile, xml..,) What is date type/format defined in Oracle database.

    Can you change data type from date to string and send in the format which oracle column is expecting.

    Thursday, July 19, 2012 6:38 AM