OraFAQ Forum: Server Utilities » SQL Loader loads only 1 record

Home » RDBMS Server » Server Utilities » SQL Loader loads only 1 record (SQL*Loader: Release 11.2.0.2.0)

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

SQL Loader loads only 1 record [message #523936]

Wed, 21 September 2011 03:56

sampathku
Messages: 1
Registered: September 2011

Junior Member

Hi Barbara,
This could be a silly mistake, but the SQL loader somehow is loading only first record. The data file is a csv and the end of line character is a new line. Some text fields have multiple new lines.

Here is my control file

load data
infile '/home/devo/c0397105/RuleImport/testLoad/dummyLoad.csv'
Truncate
into table DUMMY_LOAD_TABLE
fields terminated by "," optionally enclosed by '"'
( ID "to_number(:ID)",REQUESTED_GROUP,PURPOSE,COMMENTS)

CREATE TABLE DUMMY_LOAD_TABLE
(
ID NUMBER NOT NULL,
REQUESTED_GROUP VARCHAR2(100),
PURPOSE VARCHAR2(200),
COMMENTS VARCHAR2(300),
CONSTRAINT PK_DUMMY_LOAD_TABLE PRIMARY KEY(ID) USING INDEX TABLESPACE INDEX_TS
)

3,IST Helpline,Notify HL when corporate voice systems are been changed,"8/3 - n/a for corp sys rollout
2/7 - updated for GNCS rollout"
4,IST Helpline,Notify HL when Austin voice systems are been changed,"8/3 - n/a for corp sys rollout
2/7 - updated for GNCS rollout"
5,IST Helpline,Notify HL when retail stores are been changed,"8/3 - n/a for corp sys rollout

UPDATED 7/15
We don't have Retail resource type as Dependent System now; the rule will be changed later when Dependent Systems can accept resource types other than application"

Report message to a moderator

Re: SQL Loader loads only 1 record [message #523961 is a reply to message #523936]

Wed, 21 September 2011 05:24

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

CONTINUEIF might be the answer. Here's an example based on information you posted. If your real situation differs, code might need to be changed. This solution is based on the fact that the last character in a multi-line record is "t" (in "rollout").

Furthermore, sample data you posted is invalid - the last line lacks in closing double quotes sign; also, where does "updated 7/15" belong to? That's why I modified your data a little bit.

Here's the control file:

load data
infile *
Truncate
continueif last = 't'
into table DUMMY_LOAD_TABLE
fields terminated by "," optionally enclosed by '"'
( 
  ID "to_number(:ID)",
  REQUESTED_GROUP,
  PURPOSE,
  COMMENTS
 )
 
begindata
3,IST Helpline,Notify HL when corporate voice systems are been changed,"8/3 - n/a for corp sys rollout
2/7 - updated for GNCS rollout"
4,IST Helpline,Notify HL when Austin voice systems are been changed,"8/3 - n/a for corp sys rollout
2/7 - updated for GNCS rollout"
5,IST Helpline,Notify HL when retail stores are been changed,"8/3 - n/a for corp sys rollout"
8,LF Littlefoot,Notify HL when retail stores are been changed,"8/3 - n/a for corp sys rollout
8/7 next line"

Execution (always specify and review the LOG file!):

SQL> $sqlldr scott/tiger@ora10 control=test.ctl log=test.log

SQL*Loader: Release 10.2.0.1.0 - Production on Sri Ruj 21 12:11:35 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 3
Commit point reached - logical record count 4

The result (I used COLUMN formatting so that values would be readable):

SQL> select * from dummy_load_table;

 ID REQUESTED_GROUP      PURPOSE                        COMMENTS
--- -------------------- ------------------------------ ------------------------------
  3 IST Helpline         Notify HL when corporate voice 8/3 - n/a for corp sys rollout
                          systems are been changed      2/7 - updated for GNCS rollout

  4 IST Helpline         Notify HL when Austin voice sy 8/3 - n/a for corp sys rollout
                         stems are been changed         2/7 - updated for GNCS rollout

  5 IST Helpline         Notify HL when retail stores a 8/3 - n/a for corp sys rollout
                         re been changed

  8 LF Littlefoot        Notify HL when retail stores a 8/3 - n/a for corp sys rollout
                         re been changed                8/7 next line

Here's another CONTINUEIF discussion, if you are interested in some more reading. Also, check the documentation for more information about CONTINUEIF (and other SQL*Loader options).

Report message to a moderator

Previous Topic:	Import with OS-User
Next Topic:	Data Pump job parameters

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Feb 21 18:27:50 CST 2025