none
Unable to insert datetime field in oracle via oracle adapter RRS feed

  • Question

  • Hi, all

       I have the same issue as posted here, but the answer does not work for me. So here goes again.

       I try to insert date into a Oracle table. I set EnableSafeTyping to true then date fields become type of string in the generated schema as expected. However, I am only able to insert dd-mon-yyyy format into Oracle.

       How can I include hours, minutes and seconds? I tried below but not working.

       <ns0:REC_DT>20-Oct-2012T01:01:01.001-01:0</ns0:REC_DT>
       <ns0:REC_DT>20-Oct-2012 01.01.01</ns0:REC_DT>

       I am using LOB Adapter SDK 3.0.6527.0 and Adapter Pack 3.5.6527.0.

       Thanks for any help,

    --Heng

      

    • Moved by Ben Cline1Moderator Thursday, October 25, 2012 9:46 PM related to adapters (From:BizTalk Server General)
    Wednesday, October 24, 2012 5:32 PM

Answers

  •    I found a solution for this. Actually we can leave date type as is, there is no need to set EnableSafeTyping to true. I used attribute, InlineValue, to set the value of this field as below. InlineValue is an optional attribute. The value in this field will be evaluated (executed) in Database side then the result of the evaluation will be inserted into the destination column.

      ns0:REC_DT InlineValue="to_date('31-May-99 13:45:23', 'DD-MON-YY HH24:MI:SS')"></ns0:REC_DT>

       --Heng


    Heng Ma

    • Marked as answer by HengMa Sunday, October 28, 2012 2:54 PM
    Sunday, October 28, 2012 2:53 PM

All replies

  •    I found a solution for this. Actually we can leave date type as is, there is no need to set EnableSafeTyping to true. I used attribute, InlineValue, to set the value of this field as below. InlineValue is an optional attribute. The value in this field will be evaluated (executed) in Database side then the result of the evaluation will be inserted into the destination column.

      ns0:REC_DT InlineValue="to_date('31-May-99 13:45:23', 'DD-MON-YY HH24:MI:SS')"></ns0:REC_DT>

       --Heng


    Heng Ma

    • Marked as answer by HengMa Sunday, October 28, 2012 2:54 PM
    Sunday, October 28, 2012 2:53 PM
  • When setting enablesafetyping to true, you need to set the date in the same format as the session's date format in oracle. You can change the session date format, or as you have already done, use inline values.

    -- Please mark as answered if this answers your question.

    Thursday, November 1, 2012 6:39 PM