Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    14

    Unanswered: tricky sqlloader situation

    I am trying to load a fixed length flat file into an oracle db using sqlldr.

    I need to load one record into 2 different tables( lets say A and B).

    Fields upto position 'n' go into table A and the rest of the record is to be loaded in table B. The rest of the record can potentially be split in upto 50 rows in table B, based on the value of one of the fields in A.


    3132B221110501204100099364F003709129831030099364NN 200000000000000000000000000000000020000000000070S0 1NN99364000000000000000000000000000000000000000000 000000000000000000002000000000

    Above is one record from the file.

    Note 'S01NN' part of the record. If it was 'S02NN' the recod would look like....

    3132B221110501200500099364F000109265101110099364NN 100000000002810000000000000200000000000002993640S0 2NN99364000000000000000140000000001000000000000000 000000000000000000000000000001NN000040000000000000 00141000000001000000000000000000000000000000000000 00000001

    and so on....

    Now, how do I instruct sqlldr in my control file to loop through the record and create as many records in table B as the value of a field?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You don't use SQLLDR to do this "complex" logic.
    1) Use SQLDR to load the raw data into a single work table.
    2) Write some PL/SQL to read the work table & populate the target tables

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    I agree with Anacedent.

    Why waste your precious time attempting to get sql*loader to do
    that stuff? Advanced sql*loader is cryptic at best.

    Load all lines into a temp table then write a procedure to do everything you want.

    If you have fixed length, then maybe make a temp table with two columns. One for table-A data and the other column for table-B data.
    This way you split it up a little and can write a proc from there.

    Data manipulation is so much easier if you just get it INTO the db and
    go from there.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    14
    Thanks guys!!

    I just thought of the same approach.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •