SQL*Loader question - how to handle multiple lines
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 have tried to load it via many variations of the control file. For example:
INTO TABLE tv_shows
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.
I would propose first to find out if your presumption is right.
To do that, just make a table, with one varchar2 field, large enough to load the entire record, concatenate and load whole the logical record in one field.
Then select and see what it contains
If that's the case, maybe you can easily pre-process the file to add say ! or @ character in the beginning or at the end of each line and use that as a delimiter
Click Here to Expand Forum to Full Width