SQL*Loader question - how to handle multiple lines
Hi,
This is my first exposure to sqlldr but I am having problems trying to read in a simple file. I have a file where each logical record is spread over three lines.
i.e.
1
Fred Flintstone
Cartoon
2
The Jetsons
Cartoon
3
The Goodies
Comedy
I have tried to load it via many variations of the control file. For example:
LOAD DATA
INFILE 'shows.data'
APPEND
CONCATENATE 3
INTO TABLE tv_shows
(
id 'mysequence.nextval',
Tv_id CHAR TERMINATED BY '\n',
name CHAR TERMINATED BY '\n',
show_type CHAR TERMINATED BY '\n'
)
I get errors when I try something like this (i.e. Rejected - Error on table TV_SHOWS, column TV_ID. ORA-01722: invalid number).
The data is not fixed length as can be seen by lines 2, 3, 5 & 6.
I am presuming the concatenate joins the physical rows together and tosses away the new line character. So how do you speficy where the fields end? I have looked through the various oracle documents and orafaqs and it's not clear what I need to do.
If there is a more appropriate forum whereby I should be asking such a question please let me know. Thanks.
Jon.