sqlldr inserts null when column data default exists [message #351407] |
Tue, 30 September 2008 13:27 |
TLegend33
Messages: 203 Registered: March 2005
|
Senior Member |
|
|
I am inserting rows via sqlldr using a conventional path load into a table that has a data default for a column. The data to be inserted has some nulls in the field with the default value (gender). For some reason, the default is not populated when a row is inserted.
SQL> desc sqlldr_test
Name Null? Type
----------------------------------------- -------- ----------------------------
TOTAL NUMBER(17)
DATE DATE
GENDER VARCHAR2(1)
SQL> select column_name, data_default from user_tab_columns where table_name = 'SQLLDR_TEST';
COLUMN_NAME DATA_DEFAULT
------------------------------ ------------------------------
TOTAL
DATE
GENDER 'N'
bash-2.03$ less sqlldr_test.ctl
LOAD DATA
INFILE 'sqlldr_test.txt'
INTO TABLE sqlldr_test
APPEND
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
total,
date DATE 'YYYY-MM-DD',
gender
)
sqlldr control=sqlldr_test.ctl userid=test/test1
bash-2.03$ less sqlldr_test.txt
1|2008-09-30|M
1|2008-09-30|F
1|2008-09-30||
1|2008-09-30|F
1|2008-09-30||
1|2008-09-30||
bash-2.03$ sqlldr control=sqlldr_test.ctl userid=test/test1
SQL*Loader: Release 10.2.0.1.0 - Production on Tue Sep 30 14:10:04 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 6
SQL> select * from sqlldr_test;
TOTAL DATE G
---------- ------------------- -
1 2008-09-30 00:00:00 M
1 2008-09-30 00:00:00 F
1 2008-09-30 00:00:00
1 2008-09-30 00:00:00 F
1 2008-09-30 00:00:00
1 2008-09-30 00:00:00
6 rows selected.
You'll notice that I have null values in the gender field when I'm expecting a default value of 'N'. I found this thread, but did not think it applied in this situation:
http://www.orafaq.com/forum/m/334774/67243/?srch=sqlldr+column+data+default#msg_334774
How come I'm getting null values with this insert?
Thanks.
|
|
|
|
|
|
Re: sqlldr inserts null when column data default exists [message #351674 is a reply to message #351411] |
Thu, 02 October 2008 05:03 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
A Default value for a column is used when no value is specified diring an insert into that table - ie when the column is not specified in the list of columns in an insert.
It is possible to put a null into a column with a default value.
Observe:create table test_0115 (col_1 varchar2(10) default 'A', col_2 varchar2(10));
insert into test_0115 (col_1,col_2) values (null,'Test 1');
insert into test_0115 (col_2) values ('Test 2');
SQL> select * from test_0115;
COL_1 COL_2
---------- ----------
{null} Test 1
A Test 2
|
|
|