SQL*Loader question - how to handle multiple lines
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: SQL*Loader question - how to handle multiple lines

  1. #1
    Join Date
    Aug 2007
    Posts
    1

    Talking 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.

  2. #2
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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
  •  


Click Here to Expand Forum to Full Width