OraFAQ Forum: Server Utilities » Problems when running SQL Loader with a sequence and the option direct=true

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)

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

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:

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

sqlldr usr@MYINSTANCE/psswd control=my_control_file.ctl DIRECT=TRUE

And finally here is the error I get:

Code: [Select all] [Show/ hide]

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 ?

Report message to a moderator

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

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

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.

Report message to a moderator

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

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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:

Code: [Select all] [Show/ hide]

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) ?

Report message to a moderator

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

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 07 14:14:19 CDT 2025