| Problems when running SQL Loader with a sequence and the option direct=true [message #339322] |
Thu, 07 August 2008 05:06  |
mounir
Messages: 10
Registered: August 2008
Location: Paris
|
Junior Member |
|
|
Hi all,
I have a problem when trying to run sqlldr with the direct=true option. Here is a sample of my control file:
Load DATA
INFILE 'inputfile.txt'
TRUNCATE
INTO TABLE HISTORIC_783163980
(
CYCLEID POSITION(01:25) ,
EVENTDATE "to_date('06/08/2008', 'dd/mm/yyyy')" ,
EVENTTIME CONSTANT "10:56:12" ,
EVENTID "mysequence.nextval"
)
The command I use to run the SQL Loader is:
sqlldr usr@MYINSTANCE/psswd control=my_control_file.ctl DIRECT=TRUE
And finally here is the error I get:
Record 1: Rejected - Error on table HISTORIC_783163980.
ORA-00604: une erreur s'est produite au niveau SQL recursif 1
ORA-01400: impossible d'inserer NULL dans ("TEST"."HISTORIC_783163980"."EVENTID")
I try to insert 10 rows into the table and I get ten times the same message as above. Those 10 rows could be inserted without any problem when I remove the option direct=true.
So is there any problem when using direct=true and sequences at the same time ? Is there a solution I could use to overcome this problem ?
|
|
|
|
|
|
|
|
|
|
| Re: Problems when running SQL Loader with a sequence and the option direct=true [message #339440 is a reply to message #339322] |
Thu, 07 August 2008 09:05  |
mounir
Messages: 10
Registered: August 2008
Location: Paris
|
Junior Member |
|
|
Thanks. I modified the control file by the one below and it works fine:
Load DATA
INFILE 'inputfile.txt'
TRUNCATE
INTO TABLE HISTORIC_783163980
(
CYCLEID POSITION(01:25) ,
EVENTDATE "to_date('06/08/2008', 'dd/mm/yyyy')" ,
EVENTTIME CONSTANT "10:56:12" ,
EVENTID SEQUENCE(Max)
)
However, I haven't tried it with more than 10 rows. According to what I've read in this forum the use of the SEQUENCE parameter with the direct path does not really improve the execution time of the SQL Loader in comparison with conventional path. Is there any way to fully benefit from the direct path when having to use an automatic way to fill a column (like in my example where EVENTID is the primary key) ?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
