Advice on sqlldr,Please
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Advice on sqlldr,Please

  1. #1
    Join Date
    Jan 2001
    Posts
    642
    hi,
    I have a single datafile containing data for multiple tables. Data for each table is identified by a different record type
    egs
    (datafile.txt)

    A 1001name1 age1
    B 20 deptname

    In the above datafile, I need to take A.... into emp table
    and
    B .... into dept table.

    What changes should I make in the control file to write the data into multiple tables

    Please help me
    Badrinath

  2. #2
    Join Date
    Sep 2000
    Posts
    43
    I am writing a controlfile for you. Do you want
    1001name1 age1 into one column of your oracle table or several columns?
    Same about 20 deptname - two columns?
    Alla S. Pfauntsch
    Oracle DBA
    ========================================
    "Life is what happens while you are planning something else".

  3. #3
    Join Date
    Sep 2000
    Posts
    43
    Anyway, if you need to load it into defferent columns of your multiple table, here is an example:
    controlfile:
    LOAD DATA
    INFILE acontr.dat
    REPLACE
    INTO TABLE junk10
    (col1 position(1:4),
    col2 position(5:9),
    col3 position(10:15))
    INTO TABLE junk11
    (col1 position(16:17),
    col2 position(18:26))

    Tables description:
    SVRMGR> desc junk10
    Column Name Null? Type
    ------------------------------ -------- ----
    COL1 NUMBER
    COL2 VARCHAR2(40)
    COL3 VARCHAR2(40)
    SVRMGR> desc junk11
    Column Name Null? Type
    ------------------------------ -------- ----
    COL1 NUMBER
    COL2 VARCHAR2(40))

    datafile called acontr.dat:
    1001name1 age1 20 deptname

    I just counted pistition in this line: position 1 through 4 is 1001 and I put it in col1 of table junk10. Position 5 through 9 is name1 and I put it in col2 of same tables. Sequentially you count all your positions and put them in columns you want.

    So after load you get:
    SVRMGR> select * from junk10;
    COL1 COL2 COL3
    ---------- ---------------------------------------- ----------------------------------------
    1001 name1 age1
    1 row selected.
    SVRMGR> select * from junk11;
    COL1 COL2
    ---------- ----------------------------------------
    20 deptname
    1 row selected.

    Main thing - you really need to know what part of your datafile goes into what part of oracle tables :-)

    Alla S. Pfauntsch
    Oracle DBA
    ========================================
    "Life is what happens while you are planning something else".

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