OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » SQL Loader

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

SQL Loader [message #441324]

Sat, 30 January 2010 14:11

navin_deep
Messages: 18
Registered: November 2009

Junior Member

Hi
While importing data using sqlldr, we define as below for fixed length files :

empno POSITION(01:04) INTEGER EXTERNAL,
name POSITION(06:15) CHAR,
job POSITION(17:25) CHAR )

However, I have 31 columns and the flat file span across 2 lines for a single row. In this case how will i define POSITIONS for 2nd line for each row. Do i have to change something in input flat file ?
Or there is separate syntax for POSITION in this case ?

Regards
Naveen

Report message to a moderator

Re: SQL Loader [message #441325 is a reply to message #441324]

Sat, 30 January 2010 17:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>However, I have 31 columns and the flat file span across 2 lines for a single row.

Are you sure about this?

Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version

You might need to change how the flat file gets created.

Report message to a moderator

Re: SQL Loader [message #441390 is a reply to message #441324]

Sun, 31 January 2010 03:35

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

Senior Member
Account Moderator

Check SQL*Loader documentation's Assembling Logical Records from Physical Records section which describes how to combine multiple physical records into a single logical record. Clauses that you'd use are CONCATENATE and CONTINUEIF.

Report message to a moderator

Re: SQL Loader [message #441513 is a reply to message #441324]

Mon, 01 February 2010 13:52

LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL

Senior Member

On the other hand, if your records are the same fixed length, you could do something like this:

Example: The fixed length of each physical record is 80 characters first line plus 40 characters second line = ( 80 + 40 ) plus two (2) characters line terminator for unix or four (4) characters for windoze files.

Therefore the total physical record length (for unix) would be: 122 characters.

Your controlfile should look something like this:

LOAD DATA -- Etc ...
INFILE 'MyInfile.dat' "FIX 122"
INTO -- etc ...
empno POSITION(01:04) INTEGER EXTERNAL,
name POSITION(06:15) CHAR,
job POSITION(17:25) CHAR 
-- etc --
LF1 FILLER POSITION(81:81) CHAR, -- Optional, just for clarity
next_col POSITION (82:...)
-- etc --
)

Maybe it will work for you /forum/fa/1600/0/

Report message to a moderator

Re: SQL Loader [message #441697 is a reply to message #441513]

Tue, 02 February 2010 13:40

navin_deep
Messages: 18
Registered: November 2009

Junior Member

Thanks all for your prompt reply !!!

Report message to a moderator

Previous Topic:	Data Pump Help
Next Topic:	IMP-00041: Warning: object created with compilation warnings

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 21:24:41 CDT 2024