Home » RDBMS Server » Server Utilities » how to handle nulls for date field in control file (oracle 11g)
how to handle nulls for date field in control file [message #613206] Wed, 30 April 2014 14:07 Go to next message
vkanuri
Messages: 20
Registered: August 2013
Location: United States
Junior Member
Hi Following is one of the record which is getting rejected while I am loading using sql * loader.I understand that this is because of null in date_23 field.Target table does not have any constraints on date fields.
0000005000#!~!#0000000005#!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!#2005-01-01#!~!#2004-02-02#!~!#          #!~!#2005-01-01#!~!#2004-02-02#!~!#2020-12-31#!~!#          #!~!#          #!~!#          #!~!#


Following is the control file
OPTIONS (MULTITHREADING=TRUE,PARALLEL=TRUE,SILENT=(HEADER, FEEDBACK))
LOAD DATA
APPEND
INTO TABLE abc
WHEN
      (store      !=BLANKS)and(group_id   !=BLANKS)
FIELDS TERMINATED BY '#!~!#'
TRAILING NULLCOLS
(
           store           INTEGER EXTERNAL,
           group_id        INTEGER EXTERNAL,
           varchar2_1      CHAR,
           varchar2_2      CHAR,
           varchar2_3      CHAR,
           varchar2_4      CHAR,
           varchar2_5      CHAR,
           varchar2_6      CHAR,
           varchar2_7      CHAR,
           varchar2_8      CHAR,
           varchar2_9      CHAR,
           varchar2_10     CHAR,
           number_11       INTEGER EXTERNAL,
           number_12       INTEGER EXTERNAL,
           number_13       INTEGER EXTERNAL,
           number_14       INTEGER EXTERNAL,
           number_15       INTEGER EXTERNAL,
           number_16       INTEGER EXTERNAL,
           number_17       INTEGER EXTERNAL,
           number_18       INTEGER EXTERNAL,
           number_19       INTEGER EXTERNAL,
           number_20       INTEGER EXTERNAL,
           date_21         DATE 'yyyy-mm-dd',
           date_22         DATE 'yyyy-mm-dd',
           date_23         DATE 'yyyy-mm-dd',
           date_24         DATE 'yyyy-mm-dd',
           date_25         DATE 'yyyy-mm-dd',
           date_26         DATE 'yyyy-mm-dd',
           date_27         DATE 'yyyy-mm-dd',
           date_28         DATE 'yyyy-mm-dd',
           date_29         DATE 'yyyy-mm-dd',
           date_30         DATE 'yyyy-mm-dd'
)



Thanks
Re: how to handle nulls for date field in control file [message #613208 is a reply to message #613206] Wed, 30 April 2014 14:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Why do you think it is DATE_23 is a problem?

It appears the table has 10 DATE columns, but by my count the posted record only contains 8 fields for DATE datatype.
Is this correct?

What value should go into the table when the data does not exist (spaces or NULL) in file?
Re: how to handle nulls for date field in control file [message #613211 is a reply to message #613206] Wed, 30 April 2014 22:40 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You can create a user-defined function to check the dates, then use that in your control file, as demonstrated below.

SCOTT@orcl12c> host type test.dat
0000005000#!~!#0000000005#!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!##!~!#
2005-01-01#!~!#2004-02-02#!~!#          #!~!#2005-01-01#!~!#2004-02-02#!~!#2020-12-31#!~!#          #!~!#          #!~!#
#!~!#

SCOTT@orcl12c> host type test.ctl
OPTIONS (MULTITHREADING=TRUE,PARALLEL=TRUE,SILENT=(HEADER, FEEDBACK))
LOAD DATA
APPEND
INTO TABLE abc
WHEN
(store      !=BLANKS)and(group_id   !=BLANKS)
FIELDS TERMINATED BY '#!~!#'
TRAILING NULLCOLS
(
store           INTEGER EXTERNAL,
group_id        INTEGER EXTERNAL,
varchar2_1      CHAR,
varchar2_2      CHAR,
varchar2_3      CHAR,
varchar2_4      CHAR,
varchar2_5      CHAR,
varchar2_6      CHAR,
varchar2_7      CHAR,
varchar2_8      CHAR,
varchar2_9      CHAR,
varchar2_10     CHAR,
number_11       INTEGER EXTERNAL,
number_12       INTEGER EXTERNAL,
number_13       INTEGER EXTERNAL,
number_14       INTEGER EXTERNAL,
number_15       INTEGER EXTERNAL,
number_16       INTEGER EXTERNAL,
number_17       INTEGER EXTERNAL,
number_18       INTEGER EXTERNAL,
number_19       INTEGER EXTERNAL,
number_20       INTEGER EXTERNAL,
date_21         "my_to_date (:date_21)",
date_22         "my_to_date (:date_22)",
date_23         "my_to_date (:date_23)",
date_24         "my_to_date (:date_24)",
date_25         "my_to_date (:date_25)",
date_26         "my_to_date (:date_26)",
date_27         "my_to_date (:date_27)",
date_28         "my_to_date (:date_28)",
date_29         "my_to_date (:date_29)",
date_30         "my_to_date (:date_30)"
)

SCOTT@orcl12c> create table abc
  2  (
  3             store           NUMBER,
  4             group_id        NUMBER,
  5             varchar2_1      VARCHAR2(11),
  6             varchar2_2      VARCHAR2(11),
  7             varchar2_3      VARCHAR2(11),
  8             varchar2_4      VARCHAR2(11),
  9             varchar2_5      VARCHAR2(11),
 10             varchar2_6      VARCHAR2(11),
 11             varchar2_7      VARCHAR2(11),
 12             varchar2_8      VARCHAR2(11),
 13             varchar2_9      VARCHAR2(11),
 14             varchar2_10     VARCHAR2(11),
 15             number_11       NUMBER,
 16             number_12       NUMBER,
 17             number_13       NUMBER,
 18             number_14       NUMBER,
 19             number_15       NUMBER,
 20             number_16       NUMBER,
 21             number_17       NUMBER,
 22             number_18       NUMBER,
 23             number_19       NUMBER,
 24             number_20       NUMBER,
 25             date_21         DATE,
 26             date_22         DATE,
 27             date_23         DATE,
 28             date_24         DATE,
 29             date_25         DATE,
 30             date_26         DATE,
 31             date_27         DATE,
 32             date_28         DATE,
 33             date_29         DATE,
 34             date_30         DATE
 35  )
 36  /

Table created.

SCOTT@orcl12c> CREATE OR REPLACE FUNCTION my_to_date
  2    (p_string IN VARCHAR2)
  3    RETURN DATE
  4  AS
  5  BEGIN
  6    IF LTRIM (p_string) IS NULL THEN RETURN NULL;
  7    ELSE RETURN TO_DATE (p_string, 'yyyy-mm-dd');
  8    END IF;
  9  END my_to_date;
 10  /

Function created.

SCOTT@orcl12c> SHOW ERRORS
No errors.
SCOTT@orcl12c> host sqlldr scott/tiger control=test.ctl data=test.dat log=test.log

SQL*Loader: Release 12.1.0.1.0 - Production on Wed Apr 30 20:37:52 2014

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



SCOTT@orcl12c> select * from abc
  2  /

     STORE   GROUP_ID VARCHAR2_1  VARCHAR2_2  VARCHAR2_3  VARCHAR2_4
---------- ---------- ----------- ----------- ----------- -----------
VARCHAR2_5  VARCHAR2_6  VARCHAR2_7  VARCHAR2_8  VARCHAR2_9  VARCHAR2_10
----------- ----------- ----------- ----------- ----------- -----------
 NUMBER_11  NUMBER_12  NUMBER_13  NUMBER_14  NUMBER_15  NUMBER_16  NUMBER_17
---------- ---------- ---------- ---------- ---------- ---------- ----------
 NUMBER_18  NUMBER_19  NUMBER_20 DATE_21         DATE_22         DATE_23
---------- ---------- ---------- --------------- --------------- ---------------
DATE_24         DATE_25         DATE_26         DATE_27         DATE_28
--------------- --------------- --------------- --------------- ---------------
DATE_29         DATE_30
--------------- ---------------
      5000          5


                                 Sat 01-Jan-2005 Mon 02-Feb-2004
Sat 01-Jan-2005 Mon 02-Feb-2004 Thu 31-Dec-2020



1 row selected.

Previous Topic: importing data only in existing tables.
Next Topic: SQLLDR80 Insertion problem
Goto Forum:
  


Current Time: Tue Mar 19 06:13:20 CDT 2024