OraFAQ Forum: Server Utilities » sqlldr inserts null when column data default exists

Home » RDBMS Server » Server Utilities » sqlldr inserts null when column data default exists (Oracle 10.2 RHEL4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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.

Report message to a moderator

Re: sqlldr inserts null when column data default exists [message #351408 is a reply to message #351407]

Tue, 30 September 2008 13:59

Mahesh Rajendran
Messages: 10707
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.

Report message to a moderator

Re: sqlldr inserts null when column data default exists [message #351410 is a reply to message #351408]

Tue, 30 September 2008 14:08

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!

Report message to a moderator

Re: sqlldr inserts null when column data default exists [message #351411 is a reply to message #351410]

Tue, 30 September 2008 14:12

Mahesh Rajendran
Messages: 10707
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.

Report message to a moderator

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

Report message to a moderator

Previous Topic:	another sql*loader doubt with excel sheet
Next Topic:	manage data availability with limited quota

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 07 12:37:52 CDT 2024