-
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.
-
Hi,
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|