5 Replies Latest reply: Mar 27, 2013 7:46 AM by Mohamed Houri RSS

    sqlldr dates loading help

    carmac
      Hi all,

      I tried to load some dates using sqlldr. but those are loading to another dates.

      Ex:

      Input data file is:

      01/01/1990
      01/02/1992
      02/03/1998
      03/04/2000

      loaded dates are:

      01/01/2090
      01/02/2092
      02/03/2098
      03/04/2000


      1990 is converted to 2090.

      in sqlldr i used the code for loading is

      tmp_date date to_date(tmp_date,'MM/DD/YYYY')


      Please help me

      Thanks
        • 1. Re: sqlldr dates loading help
          jeneesh
          Can you paste your actual data (sample records) and actual control file?
          • 2. Re: sqlldr dates loading help
            carmac
            Data file data is:

            01/01/1990
            01/02/1992
            02/03/1998
            03/04/2000

            Control file is

            LOAD DATA
            append
            into table temp
            trailing nulls
            (
            tmp_date date "to_date(tmp_date,'MM/DD/YYYY')"
            )
            • 3. Re: sqlldr dates loading help
              Karthick_Arp
              You need to change your control file. Following is my data and control file
              karthick% cat test_file.dat
              01/01/1990
              01/02/1992
              02/03/1998
              03/04/2000
              
              karthick% cat test_control.ctl
              load data
              infile *
              replace
              into table test_tbl
              (  
                 tmp_date position(1:10) "to_date(:tmp_date,'MM/DD/YYYY')"
              )
              Now i execute sql loader
              karthick% sqlldr karthick/######@##### control=test_control.ctl data=test_file.dat
               
              SQL*Loader: Release 10.2.0.5.0 - Production on Wed Mar 27 07:48:35 2013
               
              Copyright (c) 1982, 2007, Oracle.  All rights reserved.
               
              Commit point reached - logical record count 4
              and data in table
              Connected to:
              Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
              With the Partitioning, OLAP, Data Mining and Real Application Testing options
               
              SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
               
              Session altered.
               
              SQL> select * from test_tbl;
               
              TMP_DATE
              --------------------
              01-JAN-1990 00:00:00
              02-JAN-1992 00:00:00
              03-FEB-1998 00:00:00
              04-MAR-2000 00:00:00
               
              SQL> 
              • 4. Re: sqlldr dates loading help
                jeneesh
                carmac wrote:
                Data file data is:

                01/01/1990
                01/02/1992
                02/03/1998
                03/04/2000

                Control file is

                LOAD DATA
                append
                into table temp
                trailing nulls
                (
                tmp_date date "to_date(tmp_date,'MM/DD/YYYY')"
                )
                The control file syntax is wrong...
                D:\test>type test_data.txt
                01/01/1990
                01/02/1992
                02/03/1998
                03/04/2000
                D:\test>type test.ctl
                load data
                into table test
                fields terminated by ','
                (
                dt "to_date(:dt,'mm/dd/yyyy')"
                )
                
                
                
                D:\test>sqlldr scott/tiger data=D:\test\test_data.txt control=D:\test\test.ctl
                
                SQL*Loader: Release 11.2.0.1.0 - Production on Wed Mar 27 17:26:47 2013
                
                Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
                
                Commit point reached - logical record count 3
                Commit point reached - logical record count 4
                
                
                SQL> select * from test;
                
                DT          C1
                ----------- ----------
                01-jan-1990
                02-jan-1992
                03-feb-1998
                04-mar-2000
                Edited by: jeneesh on Mar 27, 2013 5:30 PM
                • 5. Re: sqlldr dates loading help
                  Mohamed Houri
                  Here a simple example
                  input.dat
                  01/01/1990,
                  01/02/1992,
                  02/03/1998,
                  03/04/2000,
                  
                  LOAD DATA 
                  INFILE 'input.dat'
                  append
                  into table t1
                  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
                  TRAILING NULLCOLS
                  (
                  tmp_date date 'MM/DD/YYYY'
                  )
                  
                  SQL> select * from t1;
                  
                  no rows selected
                  
                  SQL> desc t1;
                             Name                            Null?    Type
                             ------------------------------- -------- ------------------
                      1      TMP_DATE                                 DATE
                       
                  C:\>sqlldr user/passwd@database control=ctl_file.ctl
                  
                  SQL*Loader: Release 10.2.0.3.0 - Production on Wed Mar 27 13:44:06 2013
                  
                  Copyright (c) 1982, 2005, Oracle.  All rights reserved.
                  
                  Commit point reached - logical record count 13
                  Commit point reached - logical record count 14     
                  
                  SQL> alter session set nls_date_format='DD/MM/YYYY';
                  
                  Session altered.
                  
                  SQL> select * from t1;
                  
                  TMP_DATE
                  ----------
                  01/01/1990
                  02/01/1992
                  03/02/1998
                  04/03/2000
                  
                  SQL> alter session set nls_date_format='DD-MON-YYYY';
                  
                  Session altered.
                  
                  SQL> select * from t1;
                  
                  TMP_DATE
                  -----------
                  01-JAN-1990
                  02-JAN-1992
                  03-FEB-1998
                  04-MAR-2000
                  
                  {code}
                  
                  Best Regards
                  Mohamed Houri
                  www.hourim.wordpress.com