5 Replies Latest reply: Jan 11, 2013 5:15 PM by 984439 RSS

    Control File Date/Time Field

    878812
      Hello all,

      Im using SQL Loader to load a text file into a database table. Im having an issue with one of the fields that contains date and time. Here's what I have:

      Data:

      Customer 1|850510|C2117-2972A|2011-07-07 00:00:00|21172972|BA
      Customer 2|850511|C2118-2972A|2011-04-07 00:00:00|21172972|BA
      Customer 3|850512|C2119-2972A|2011-12-07 00:00:00|21172972|BA
      Customer 4|850513|C2120-2972A|2011-11-07 00:00:00|21172972|BA
      Customer 5|850514|C2121-2972A|2011-10-07 00:00:00|21172972|BA
      Customer 6|850515|C2122-2972A|2011-02-07 00:00:00|21172972|BA
      Customer 7|850516|C2123-2972A|2011-01-07 00:00:00|21172972|BA

      Control File:

      LOAD DATA
      APPEND
      INTO TABLE MYTBL
      FIELDS TERMINATED BY "|"
      TRAILING NULLCOLS
      (
           response_key "response_seq.NEXTVAL",
           response_dt "sysdate",
           response_cust,
           response_mcn INTEGER,
           response_isc,
           response_id "to_date(:response_id,'YYYY-MM-DD HH:MI:SS')",
           response_ordernum INTEGER,
           response_too
      )

      LOG Results:

      Column Name Position Len Term Encl Datatype
      ------------------------------ ---------- ----- ---- ---- ---------------------
      RESPONSE_KEY FIRST * | CHARACTER
      SQL string for column : "seq_response_seq.NEXTVAL"
      RESPONSE_DT NEXT * | CHARACTER
      SQL string for column : "sysdate"
      RESPONSE_CUST NEXT * | CHARACTER
      RESPONSE_MCN NEXT 4 INTEGER
      RESPONSE_ISC NEXT * | CHARACTER
      RESPONSE_ID NEXT * | CHARACTER
      SQL string for column : "to_date(:response_id,'YYYY-MM-DD HH:MI:SS')"
      RESPONSE_ORDERNUM NEXT 4 INTEGER
      RESPONSE_TOO NEXT * | CHARACTER


      Record 1: Rejected - Error on table "MYDB"."MYTBL", column RESPONSE_ID.
      ORA-01861: literal does not match format string

      Record 2: Rejected - Error on table "MYDB"."MYTBL", column RESPONSE_ID.
      ORA-01861: literal does not match format string

      Record 3: Rejected - Error on table "MYDB"."MYTBL", column RESPONSE_ID.
      ORA-01861: literal does not match format string

      Record 4: Rejected - Error on table "MYDB"."MYTBL", column RESPONSE_ID.
      ORA-01861: literal does not match format string

      Record 5: Rejected - Error on table "MYDB"."MYTBL", column RESPONSE_ID.
      ORA-01861: literal does not match format string

      Record 6: Rejected - Error on table "MYDB"."MYTBL", column RESPONSE_ID.
      ORA-01861: literal does not match format string

      Record 7: Rejected - Error on table "MYDB"."MYTBL", column RESPONSE_ID.
      ORA-01861: literal does not match format string

      How do I format the date correctly?

      Thanks.

      Edited by: 875809 on Jul 29, 2011 1:07 PM
        • 1. Re: Control File Date/Time Field
          Srini Chavali-Oracle
          Pl see if MOS Doc 431382.1 (ORA-1861 During Import or Table Creation) can help

          HTH
          Srini
          • 2. Re: Control File Date/Time Field
            DBA_1976
            post the result of
            desc MYTABLE;
            • 3. Re: Control File Date/Time Field
              Brian Bontrager
              I suspect your data fields aren't lining up with your control file the way you expect them to...

              For example, using the first record
              Customer 1|850510|C2117-2972A|2011-07-07 00:00:00|21172972|BA

              response_key = "Customer 1" , overridden by response_seq.NEXTVAL
              response_dt = "850510" , overridden by sysdate
              response_cust = "C2117-2972A"
              response_mcn = "2011"
              response_isc = "-07-07 00:00:00"
              response_id = "21172972", with the function attempted as "to_date('21172971','YYYY-MM-DD HH:MI:SS')",
              response_ordernum = "BA"
              response_too = ???

              You might consider EXPRESSION fields, which let you insert a calculated value, but don't replace a data field in the source record:
              LOAD DATA
              APPEND
              INTO TABLE MYTBL 
              FIELDS TERMINATED BY "|"
              TRAILING NULLCOLS
              (
              response_key EXPRESSION "response_seq.NEXTVAL",
              response_dt EXPRESSION "sysdate",
              response_cust,
              response_mcn INTEGER,
              response_isc,
              response_id "to_date(:response_id,'YYYY-MM-DD HH:MI:SS')",
              response_ordernum INTEGER,
              response_too
              )
              which results in

              response_key = response_seq.NEXTVAL
              response_dt = sysdate
              response_cust = "Customer 1"
              response_mcn = "850510"
              response_isc = "C2117-2972A"
              response_id = "2011-07-07 00:00:00", with the function interpreted as "to_date('2011-07-07 00:00:00','YYYY-MM-DD HH:MI:SS')",
              response_ordernum = "21172972"
              response_too = "BA"
              • 4. Re: Control File Date/Time Field
                Brian Bontrager
                There may also be an issue with INTEGER, as opposed to INTEGER EXTERNAL, since the log file only shows the INTEGER columns taked the "NEXT 4" instead of up to the next delimiter?
                • 5. Re: Control File Date/Time Field
                  984439
                  Hi,
                  i want to load one of the Oracle fields in Oracle with a date which is 30 days after SYSDATE. Can anyone please let me know how to write the command on the CTL file for this.

                  i have tried

                  Oracle_Date_column constant "SYSDATE+30"
                  Oracle_Date_column constant SYSDATE+30
                  Oracle_Date_column date "SYSDATE+30"
                  Oracle_Date_column trunc(SYSDATE)+30

                  None of these worked.

                  Thanks.