Home » RDBMS Server » Server Utilities » SQL Loader
SQL Loader [message #441324] Sat, 30 January 2010 14:11 Go to next message
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
Re: SQL Loader [message #441325 is a reply to message #441324] Sat, 30 January 2010 17:44 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
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.
Re: SQL Loader [message #441390 is a reply to message #441324] Sun, 31 January 2010 03:35 Go to previous messageGo to next message
Littlefoot
Messages: 20827
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.
icon12.gif  Re: SQL Loader [message #441513 is a reply to message #441324] Mon, 01 February 2010 13:52 Go to previous messageGo to next message
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 ./fa/1600/0/
+
Re: SQL Loader [message #441697 is a reply to message #441513] Tue, 02 February 2010 13:40 Go to previous message
navin_deep
Messages: 18
Registered: November 2009
Junior Member
Thanks all for your prompt reply !!!
Previous Topic: Data Pump Help
Next Topic: IMP-00041: Warning: object created with compilation warnings
Goto Forum:
  


Current Time: Tue Sep 27 21:13:05 CDT 2016

Total time taken to generate the page: 0.10038 seconds