11 Replies Latest reply: Jul 3, 2013 12:34 PM by Barbara Boehmer RSS

    sql loader -  to_date() issue

    BITS
      How do I load the date field from the CSV *"03-Dec-09 10.06.58.00 PM"* into oracle tables using sql loader ?

      The contol file is as follows:

      LOAD DATA
      INTO TABLE
      CUSTOMER
      FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
      (CUST_ID,
      ORDER_DATE "to_date(:ORDER_DATE, 'DD-MON-YY HH:MI:SS.TT AM')"

      but it doesn't work, I also tried _ORDER_DATE "DD/MM/YYYY"_ with no luck, any suggestion ?
      I am kind of new in using SQL LOADER.

      thanks.
        • 1. Re: sql loader -  to_date() issue
          Srini Chavali-Oracle
          Pl post details of OS and database versions. What does "doesn't work" or "no luck" mean ? Can you provide examples of what exactly was loaded ?

          HTH
          Srini
          • 2. Re: sql loader -  to_date() issue
            BITS
            OS n ORACEL version
            Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

            CSV File example :-

            CUST_ID, ORDER_DATE
            1,03-Dec-10 10.06.58.00 PM
            2,03-Dec-09 10.07.18.00 PM
            3,03-Dec-11 04.07.18.00 AM
            4,01-Dec-07 10.07.18.00 PM
            5,03-Dec-10 10.07.18.00 AM
            6,12-Dec-10 10.07.18.00 PM

            CONTROL FILE Which "doesn't work "

            LOAD DATA
            INTO TABLE
            CUSTOMER
            FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
            (CUST_ID,
            ORDER_DATE "to_date(:ORDER_DATE, 'DD-MON-YY HH:MI:SS.TT AM')"

            I hope this provides complete info about my issue.

            Thanks
            • 3. Re: sql loader -  to_date() issue
              Srini Chavali-Oracle
              Pl post a description of the table, the SQL*Loader log file and the output of a select statement to show what was loaded into the database columns. What is the OS ?

              HTH
              Srini
              • 4. Re: sql loader -  to_date() issue
                BITS
                OS Version:
                Linux version 2.6.16.60
                ===================================================================
                DESC CUSTOMER

                SQL> desc customer;
                Name Null? Type
                ----------------------------------------- -------- ----------------------------
                CUST_ID NUMBER(10)
                ORDER_DATE DATE


                ====================================================================
                There is no data in the table.
                ====================================================================
                SQL LOADER LOG FILE_

                SQL*Loader: Release 10.2.0.4.0 - Production on Wed Sep 7 11:16:50 2011

                Copyright (c) 1982, 2007, Oracle. All rights reserved.

                Control File: C:\XXXXX\xxxxxx\test1.ctl
                Data File: C:\XXXXX\xxxxxx\test1.csv
                Bad File: C:\XXXXX\xxxxxx\test1.bad
                Discard File: C:\XXXXX\xxxxxx\test1.dsc
                (Allow all discards)

                Number to load: ALL
                Number to skip: 1
                Errors allowed: 50
                Bind array: 64 rows, maximum of 256000 bytes
                Continuation: none specified
                Path used: Conventional

                Table "XXXXXXX"."CUSTOMER", loaded from every logical record.
                Insert option in effect for this table: INSERT
                TRAILING NULLCOLS option in effect

                Column Name Position Len Term Encl Datatype
                ------------------------------ ---------- ----- ---- ---- ---------------------
                CUST_ID FIRST * , O(") CHARACTER
                ORDER_DATE NEXT * , O(") CHARACTER
                SQL string for column : "to_date(:ORDER_DATE, 'DD-MON-YY HH:MI:SS.TT AM')"

                Record 1: Rejected - Error on table "XXXXXX"."CUSTOMER", column ORDER_DATE.
                ORA-01821: date format not recognized

                Record 2: Rejected - Error on table "XXXXXX"."CUSTOMER", column ORDER_DATE.
                ORA-01821: date format not recognized

                Record 3: Rejected - Error on table "XXXXXX"."CUSTOMER", column ORDER_DATE.
                ORA-01821: date format not recognized

                Record 4: Rejected - Error on table "XXXXXX"."CUSTOMER", column ORDER_DATE.
                ORA-01821: date format not recognized

                Record 5: Rejected - Error on table "XXXXXX"."CUSTOMER", column ORDER_DATE.
                ORA-01821: date format not recognized

                Record 6: Rejected - Error on table "XXXXXX"."CUSTOMER", column ORDER_DATE.
                ORA-01821: date format not recognized

                Record 7: Rejected - Error on table "XXXXXX"."CUSTOMER", column ORDER_DATE.
                ORA-01821: date format not recognized

                Record 8: Rejected - Error on table "XXXXXX"."CUSTOMER", column ORDER_DATE.
                ORA-01821: date format not recognized

                Record 9: Rejected - Error on table "XXXXXX"."CUSTOMER", column ORDER_DATE.
                ORA-01821: date format not recognized

                Record 10: Rejected - Error on table "XXXXXX"."CUSTOMER", column ORDER_DATE.
                ORA-01821: date format not recognized
                ===============================================================================
                • 5. Re: sql loader -  to_date() issue
                  Srini Chavali-Oracle
                  Are you wanting to store the time component of the input data in the ORDER_DATE field ? Or only the date ?

                  Srini
                  • 6. Re: sql loader -  to_date() issue
                    BITS
                    Yes, Time component is required.

                    Thanks !!!
                    • 7. Re: sql loader -  to_date() issue
                      user130038
                      The CSV sample you provided uses " *.* " in the time-component [ 10.06.58.00 ] while your SQL Loader Control file is expecting " *:* " [ HH:MI:SS.TT ]. These two formats are obviously not the same. Update your control file and try again.
                      CUST_ID, ORDER_DATE
                      1,03-Dec-10 10.06.58.00 PM
                      2,03-Dec-09 10.07.18.00 PM
                      3,03-Dec-11 04.07.18.00 AM
                      4,01-Dec-07 10.07.18.00 PM
                      5,03-Dec-10 10.07.18.00 AM
                      6,12-Dec-10 10.07.18.00 PM
                      CONTROL FILE Which "doesn't work "
                      
                      LOAD DATA
                      INTO TABLE
                      CUSTOMER
                      FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
                      (CUST_ID,
                      ORDER_DATE "to_date(:ORDER_DATE, 'DD-MON-YY HH:MI:SS.TT AM')"
                      {code}
                      
                      Edited by: user130038 on Sep 9, 2011 6:56 AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                      • 8. Re: sql loader -  to_date() issue
                        BITS
                        Nope it didn't work.
                        • 9. Re: sql loader -  to_date() issue
                          mboliganDD
                          I think your time format is wrong. It looks like you have HH.MI.SS, but your format says HH:MI.SS.

                          HTH,
                          Mike
                          • 10. Re: sql loader -  to_date() issue
                            SriramKarthik

                            Hi,

                             

                            Use the below format if u feel to know still.

                             

                            LOAD DATA
                            INTO TABLE
                            CUSTOMER
                            FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
                            (CUST_ID,

                            ORDER_DATE Date "DD-MON-YY HH12.MI.SS.FF2 AM",

                            {code}

                            • 11. Re: sql loader -  to_date() issue
                              Barbara Boehmer

                              The data is timestamp data, so it needs to be loaded as a timestamp, then Oracle can implicitly convert it to date when it inserts into the date column, as demonstrated below.

                               

                              SCOTT@orcl_11gR2> host type test1.csv

                              CUST_ID, ORDER_DATE

                              1,03-Dec-10 10.06.58.00 PM

                              2,03-Dec-09 10.07.18.00 PM

                              3,03-Dec-11 04.07.18.00 AM

                              4,01-Dec-07 10.07.18.00 PM

                              5,03-Dec-10 10.07.18.00 AM

                              6,12-Dec-10 10.07.18.00 PM

                               

                              SCOTT@orcl_11gR2> host type test1.ctl

                              LOAD DATA

                              INTO TABLE CUSTOMER

                              FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

                              trailing nullcols

                              (CUST_ID,

                              ORDER_DATE timestamp "DD-Mon-YY HH.MI.SS.FF AM"

                              )

                               

                              SCOTT@orcl_11gR2> select * from v$version

                                2  /

                               

                              BANNER

                              --------------------------------------------------------------------------------

                              Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

                              PL/SQL Release 11.2.0.1.0 - Production

                              CORE    11.2.0.1.0      Production

                              TNS for 64-bit Windows: Version 11.2.0.1.0 - Production

                              NLSRTL Version 11.2.0.1.0 - Production

                               

                              5 rows selected.

                               

                              SCOTT@orcl_11gR2> create table customer

                                2    (cust_id     number(10),

                                3     order_date  date)

                                4  /

                               

                              Table created.

                               

                              SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test1.ctl data=test1.csv bad=test1.bad discard=test1.dsc log=test.log

                               

                              SQL*Loader: Release 11.2.0.1.0 - Production on Wed Jul 3 10:31:36 2013

                               

                              Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

                               

                              Commit point reached - logical record count 7

                               

                              SCOTT@orcl_11gR2> select * from customer

                                2  /

                               

                                 CUST_ID ORDER_DATE

                              ---------- -----------------------

                                       1 03-DEC-2010 10:06:58 PM

                                       2 03-DEC-2009 10:07:18 PM

                                       3 03-DEC-2011 04:07:18 AM

                                       4 01-DEC-2007 10:07:18 PM

                                       5 03-DEC-2010 10:07:18 AM

                                       6 12-DEC-2010 10:07:18 PM

                               

                              6 rows selected.