Home » RDBMS Server » Server Utilities » Problems when running SQL Loader with a sequence and the option direct=true (SQL*Loader: Release 10.2.0.3.0; Oracle Version: 10g; OS: UNIX)
Problems when running SQL Loader with a sequence and the option direct=true [message #339322] Thu, 07 August 2008 05:06 Go to next message
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 #339339 is a reply to message #339322] Thu, 07 August 2008 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ This is in the doc
2/ This has been asked a couple of weeks ago
3/ Why do you direct=true for 10 rows???

Regards
Michel
Re: Problems when running SQL Loader with a sequence and the option direct=true [message #339356 is a reply to message #339339] Thu, 07 August 2008 06:12 Go to previous messageGo to next message
mounir
Messages: 10
Registered: August 2008
Location: Paris
Junior Member
Tnx for the answer.

I looked for the problem on this forum but couldn't find any relevant answer. Could you please send me a link to the thread ?

Concerning the 10 rows I try to insert, that's only for testing. I'll be actually dealing with dozens of millions of rows.
Re: Problems when running SQL Loader with a sequence and the option direct=true [message #339359 is a reply to message #339356] Thu, 07 August 2008 06:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't search very well:
http://www.orafaq.com/forum/?SQ=e07fb1585050c0023334d45fd7751842&t=search&srch=sequence+direct&btn_submit=Search&fiel d=all&forum_limiter=10&search_logic=AND&sort_order=DESC&author=

Regards
Michel
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 Go to previous messageGo to next message
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) ?
Re: Problems when running SQL Loader with a sequence and the option direct=true [message #339473 is a reply to message #339440] Thu, 07 August 2008 10:07 Go to previous message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No one I am aware of but Barbara is our expert on SQL*Loader here and she will may have one of her neat ideas.

Regards
Michel
Previous Topic: Reconcillation of data loaded thro sqlloader to remote server
Next Topic: export a single row
Goto Forum:
  


Current Time: Wed Dec 07 12:29:23 CST 2016

Total time taken to generate the page: 0.12662 seconds