SQL Loader is not inserting data when NULL

Question

I have the following table:

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
NAME                                               VARCHAR2(230)
TEST                                               VARCHAR2(230)
ID6                                                VARCHAR2(230)
ID4                                                VARCHAR2(230)
ID1                                                VARCHAR2(230)
ID2                                                VARCHAR2(230)
ID3                                                VARCHAR2(230)
ID5                                                VARCHAR2(230)
ID                                                 NUMBER(38)

Case 1:

Now I try to insert data through sqlldr. It loads data when values are provided for all of the columns, as follows:

a,b,c,d,e,f,g,1
a,b,c,d,e,f,g,1
a,b,c,d,e,f,g,1
a,b,c,d,e,f,g,1
a,b,c,d,e,f,g,1

Case 2:

when I try to load data like so:

a,,,,,,,

data is not loaded.

I also tried using this way:

a,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL

but got the message:

Commit point reached - logical record count 1

When I take count on that table, it remains 0.

Can anyone help me load the data from case 2?

My contol file:

LOAD DATA INFILE 'temp.txt' 
BADFILE 'temp_log.txt' 
INTO TABLE a 
FIELDS TERMINATED BY "," (
name,
test,
id6,
id4,
id1,
id2,
id3,
id5,
id)

LOAD DATA INFILE 'temp.txt' BADFILE 'temp_log.txt' INTO TABLE a FIELDS TERMINATED BY "," (name,test,id6,id4,id1,id2,id3,id5,id) — Madhu Velayudhan, Apr 12 '14 at 12:04
What does the log file say? (Not the `badfile`, that wil just show the skipped record; the actual `.log`) — Alex Poole, Apr 12 '14 at 15:32
where can i find that log file.. same folder as control.ctl file?? — Madhu Velayudhan, Apr 12 '14 at 15:35
By default it has the same name as the control file, but with a `.log` extension instead of `.ctl`. And yes, in the same directory. — Alex Poole, Apr 12 '14 at 20:05
you need a comma after the last value, or add trailing null colls to the ctl file — user1645975, Apr 15 '14 at 12:17

score 0 · Answer 1 · answered Aug 13 '14 at 06:04

try the following code i had tried it.its working

a sample data is taken for test in temp.txt

a,,,,,,,

use "trailing nullcols" for inserting null data in the table

LOAD DATA 
INFILE 'temp.txt' 
BADFILE 'temp_log.txt' 
INTO TABLE a 
FIELDS TERMINATED BY "," 
trailing nullcols
(name,
test,
id6,
id4,
id1,
id2,
id3,
id5,
id)

writing the control file.Let the control file is saved with "samp.ctl" and the data file is saved as temp.txt.

execute the file by using the following command in the command prompt syntax will be

sqlldr username/password@database control=control_path_file log=log_path_file

sqlldr abc/abc@orcl123 control=samp.ctl log=log_samp.log

the result will be Commit point reached - logical record count 1

SQL Loader is not inserting data when NULL

1 Answers1