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  |
 |
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 #613211 is a reply to message #613206] |
Wed, 30 April 2014 22:40  |
 |
Barbara Boehmer
Messages: 9101
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.
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 02:40:45 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
