Home » RDBMS Server » Server Utilities » sqlldr inserts null when column data default exists (Oracle 10.2 RHEL4)
sqlldr inserts null when column data default exists [message #351407] Tue, 30 September 2008 13:27 Go to next message
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 #351408 is a reply to message #351407] Tue, 30 September 2008 13:59 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
oracle@mutation#cat somectl.ctl
LOAD DATA
INFILE 'sqlldr_test.txt'
INTO TABLE test
truncate
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
total,
dat DATE 'YYYY-MM-DD',
gender "nvl(:gender,'N')"
)

oracle@mutation#sqlldr control=somectl.ctl userid=dbadmin/xxx@lawtest

SQL*Loader: Release 9.2.0.8.0 - Production on Tue Sep 30 15:00:07 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 6
oracle@mutation#query lawtest dbadmin.test

     TOTAL DAT       G
---------- --------- -
         1 30-SEP-08 M
         1 30-SEP-08 F
         1 30-SEP-08 N
         1 30-SEP-08 F
         1 30-SEP-08 N
         1 30-SEP-08 N

6 rows selected.

Re: sqlldr inserts null when column data default exists [message #351410 is a reply to message #351408] Tue, 30 September 2008 14:08 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
That's fantastic. Thank you very much.
However, why did the column data default fail with my sqlldr insert statement? Are column data defaults bypassed with sqlldr using both conventional and direct path loads?

Thanks again!
Re: sqlldr inserts null when column data default exists [message #351411 is a reply to message #351410] Tue, 30 September 2008 14:12 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I suppose so.
The database 'default' is meant for sql inserts.
sqlldr has several options to deal with with this.
Re: sqlldr inserts null when column data default exists [message #351674 is a reply to message #351411] Thu, 02 October 2008 05:03 Go to previous message
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
Previous Topic: another sql*loader doubt with excel sheet
Next Topic: manage data availability with limited quota
Goto Forum:
  


Current Time: Fri Dec 02 16:57:25 CST 2016

Total time taken to generate the page: 0.19552 seconds