SQLLoader - Contenate and continueif

[rajesh.g...@gmail.com]

I have to load the following data into a table with same structure

Cd Text
AA AAAAAAAAAAAAAAAAAAAAAAAAAAA
BB BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
CC CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCC
DD DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
EE EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE
FF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
FFFFFFFFFFFF
GG GGGGGGGGGGGGGGGGGGGGGGGGGGG

The table has 2 columns - cd, text.

The length of text in the table is varchar2(1000).

The file has only 40 characters of text for each line. If the length
exceeds 40 characters, it is moved to the next line as for the CC and
FF.

The texts across mutliple lines should be concatenated into one record

Is there an option to do this through sqlldr?

[DA Morgan]

rajesh.g...@gmail.com wrote:

Looks like fixed length data to me and SQL*Loader has had not problem
with that for well more than a decade so why are you asking the question?

1. This is school work and you didn't pay attention in class?
2. You don't know how to use the web to look up SQL*Loader syntax?
3. All of the above?

The syntax is well documented at http://tahiti.oracle.com and at
http://www.psoug.org
click on Morgan's Library
click on SQL*Loader

And my apologies to anyone trying to use the library for the last 2
days. I ran out of paid quota on the server and was out-of-town. All
should be fixed now.

Who would have ever thought I'd hit 5GB of downloads in just 10 days?
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace 'x' with 'u' to respond)

[rajesh.g...@gmail.com]

Did you read my question fully? I would not have asked this question
for simple fixed length delimiter. Please read the question and
UNDERSTAND if there is a catch in the question before blasting off.

I have to concatentate variable number of records of second field into
one field

[DA Morgan]

Can't be done the way you are trying to do it.

Blow it into a staging table and disburse from there.

[G Quesnel]

Do you have a control character in every line of input text? (or is
that the problem)
Did you try to use the CONTINUEIF option to build logical records from
multiple physical record ?
Which Oracle version are you using (the newer version have more
capabilities) ?

This is basic SQLLoader stuff, so I am missing something ?

[rajesh.g...@gmail.com]

I am using 8.1.7. I checked some examples of continueif and found that
multiple physical records are for different fields in the same logical
record. What I need to concatenate 2 physical records into the same
field. Do you know how to do that?

[MrB]

rajesh.g...@gmail.com wrote in news:1108608886.684671.282580
@z14g2000cwz.googlegroups.com:

The table definition that is the target for the load and any TERMINATED
BY clauses etc will define how the logical record is mapped on to the
individual fields.

You can nearly do what you want, but there is a problem with the leading
spaces at the start of each coninuation record.

The following sample control file will load the following data

LOAD DATA
INFILE *
DISCARDFILE loadtest.dsc
TRUNCATE
CONTINUEIF NEXT PRESERVE (1:2) = ' '
INTO TABLE loadtest
( cd CHAR(2),
text CHAR(1000)
)
BEGINDATA
A1 ZAAAAAAAAAAAAAAAAAAAAAAAAAZ
B2 XBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBX
C3 WCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCW
D4 DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
E5 EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE
F6 FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
FFFFFFFFFFFF
G7 GGGGGGGGGGGGGGGGGGGGGGGGGGG

**BUT**
The 'NEXT PRESERVE (1:2)' clause keeps the first 2 chars of
the logical record so that A1, B2 etc are kept to populate
the 'cd' column of the table. But this has the effect of keeping the
(first two) spaces from the start of the continuation records that are
included when the physical records are appended together. Result, gaps in
the 'text' field that might give you a problem.

SQL> select * from loadtest;

CD
--
TEXT
-------------------------------------------------------------------------
-------

AA
ZAAAAAAAAAAAAAAAAAAAAAAAAAZ

BB
XBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBX

CC
WCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC CCCCCCCCCCCW