Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: SQL*Loader

  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: SQL*Loader

    Hi,

    system: Win2k
    Oracle8i 8.1.7

    I have data in a spreadsheet I am trying to load using SQL*Loader.
    The top columns are the names of a table COLUMN and the left most rows are the name of
    another table NAME and the data in between is data that goes in
    another table, AMOUNT. The data in the middle is the data i want to load in the AMOUNT table.

    NAME COL_ONE COL_TWO COL_THREE
    -------------------------------------------------------------------
    RICH 17 151 640
    GILLES 20 138 450
    PATRICK 58 345 920
    DAMIEN 72 501 1069

    tables:

    NAME
    ___________
    NAME_ID pk
    NAME

    COLUMN
    ___________
    COLUMN_iD pk
    COLUMN

    AMOUNT
    __________
    AMOUNT
    NAME_ID fk ref (NAME_ID inf NAME)
    COLUMN_ID fk ref (COLUMN_ID in COLUMN)

    how can I do this in one shot; that is, how can I load the amount '17',for example, in the AMOUNT table taking into account the Foreign key from COLUMN table and the same from the NAME table?
    Can I use the select statement in SQL*Loader to look up the foreign key and add that value in so that SQL*Loader does that for each value in the amount, it picks up the COLUMN_ID from the COLUMN table and the picks up the NAME_ID from the NAME table, both associated with the amount and creates an insert statement?

    INSERT INTO AMOUNT VALUES (17,1,1);

    where 17 is the amount, 1 is the NAME_ID, and the last 1 is the COLUMN_ID.


    i tried fix positioning but ran into trouble when i had to add the pertaining values (NAME_ID and COLUMN_ID) so that each amount corresponds to the name_id and column_id to the existing amounts. given millions of records, that is not fisible, unless some of you are UltraEdit32 expersts and can tell me how to do that in that editor.

    any thoughts on that??

  2. #2
    Join Date
    Mar 2004
    Posts
    19
    Hey, there is a simple way for this.

    Create table with the columns in the spread sheet and create trigger on the new table to send data to the two tables you mentioned.


    Hope this helps
    -------------
    Pragati Swain

  3. #3
    Join Date
    Mar 2003
    Posts
    45
    Originally posted by pragatiswain
    Hey, there is a simple way for this.

    Create table with the columns in the spread sheet and create trigger on the new table to send data to the two tables you mentioned.


    Hope this helps

    THAT SOUNDS INTERESTING. I WILL DEVELOP THAT IDEA SOME MORE.
    HOWEVER, I STILL WANT TO DO IT THE WAY I FIRST STARTED OUT. IT IS FOR THE PURPOSE OF SEING WHAT SQL*LOADER IS CAPABLE OF AND TO TRAIN A CLASS.

  4. #4
    Join Date
    Mar 2004
    Posts
    19
    Hey, I am not an expert in Oracle. I just know little bit about everything.
    May be the Oracle Guru's in the Forum can help you.

    Regards
    -------------
    Pragati Swain

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    You are way better off if you load the data into a temporary table
    that replects the spreadsheet and then write a procedure to load
    your data into the appropriate table.

    I've wasted much time trying to figure out advanced sql*loader crap
    and it is not worth the time involved when you can write a proc
    to do the same work in 5% of the time it would take you to learn
    sql*loader advanced techniques which might OR MIGHT NOT work.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I highly agree with Duck !!! I am constantly loading data for clients .. I will (almost) always create a table that for the most part represents the "spreadsheet".... This table is called LOAD_tablename... Once the data is in Oracle, you can do whatever you need to do (AND QUICKER!!)

    HTH
    Gregg

  7. #7
    Join Date
    Mar 2004
    Posts
    19
    I agree with Duck and Gregg.

    Another Idea:

    You can try another option in 9i called "external table" where you can see a flat file as a table. You can insert the data anywhere you want... from that table.

    Hope this helps
    -------------
    Pragati Swain

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by pragatiswain
    I agree with Duck and Gregg.

    Another Idea:

    You can try another option in 9i called "external table" where you can see a flat file as a table. You can insert the data anywhere you want... from that table.

    Hope this helps
    that would be fun to try!
    I am gonna try that tomorrow.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    loading data

    Hi,

    I was able to load the spreadsheet into a load_data table. Now I have a table with this kind of data: note that each value is related to the name_id in the name table and the column_id in the column table. I have to load that data and make sure each value corresponds to the right amount.

    -------------------- -------------------- -------------------- ---
    17 151 640 48
    20 138 450 29
    58 345 920 109

    someone suggested that now that I have the data in oracle, i can use procedures or triggers to match the data. That is, these amounts have to be match with name_id from the name table and column_id from the column table.
    tables:
    NAME
    ___________
    NAME_ID pk
    NAME

    COLUMN
    ___________
    COLUMN_iD pk
    COLUMN

    AMOUNT
    __________
    AMOUNT
    NAME_ID fk ref (NAME_ID inf NAME)
    COLUMN_ID fk ref (COLUMN_ID in COLUMN)

    I created this but that does not work well:

    DECLARE
    CURSOR RX IS
    SELECT COL_1, COL_2, COL_3,COL_4
    FROM LOAD_DATA;

    CURSOR NAME IS
    SELECT NAME_ID
    FROM NAME
    ORDER BY NAME_ID;

    CURSOR COLUMN IS
    SELECT COLUMN_ID
    FROM COLUMN
    ORDER BY COLUMN_ID;

    R RX%ROWTYPE;
    T NAME%ROWTYPE;
    P COLUMN%ROWTYPE;



    BEGIN
    OPEN RX;
    OPEN NAME;
    OPEN COLUMN;

    LOOP
    EXIT WHEN RX%NOTFOUND;
    FETCH RX INTO R;
    LOOP
    EXIT WHEN NAME%NOTFOUND;
    FETCH NAME INTO T;
    LOOP
    EXIT WHEN COLUMN%NOTFOUND;
    FETCH COLUMN INTO P;
    DBMS_OUTPUT.PUT_LINE('INSERT INTO AMOUNT VALUES ('||RX.?||','||T.NAME||','||P.COLUMN||');');
    END LOOP;
    END LOOP;
    -- END LOOP;

    -- CLOSE RX;
    CLOSE NAME;
    CLOSE COLUMN;

    END;
    /


    but that only works partially. Is there a better way to this whereby either through a procedures or triggers or a combination of both I can load the data from the load_data table and make each value, say '17' which is row 1, column 1 of the laod_data table correspond to each name_id and column_id from the other tables?

    Your help is greatly appriciated.

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    you are doing this incorrectly.
    your procedure can directly insert into any table you want.
    you don't need to output the insert statements.

    use if statements to manipulate your data.

    so
    if RX.column = 'x' then vNewValue := '17';

    insert into amount values (
    vNewValue,
    T.name,
    v.column );

    or whatever.

    you don't want to use dbms_output.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139
    I understand what you saying, but this is a loop that has to pickup every value from left to right and associate it with its corresponding t.name_id and then p.column_id:
    17 151 640 48
    20 138 450 29
    58 345 920 109

    insert into amount values (17,1,1);
    insert into amount values (151,1,2);
    insert into amount values (640,1,3);
    insert into amount values (48,1,4);
    insert into amount values (20,2,1);
    insert into amount values (138,2,2);
    and on and on....

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296
    Since I don't know exactly what you want to do I am having a difficult time helping you.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  13. #13
    Join Date
    Mar 2003
    Posts
    45
    OK, HERE IS THE DEAL:

    TREE TABLES:

    NAME AMOUNT COLUMN

    NAME (PK) AMOUNT COLUMN_ID
    NAME_ID COLUMN_ID (FK REF COLUMN (COLUMN_ID) COLUMN_NAME
    NAME_ID (FK REF NAME (NAME_ID)

    DATA:

    NAME AMOUNT COLUMN

    1,NEW YORK 1,BUIL_1
    2,SAN FRANCISCO 2,BUIL_2



    THE NAME TABLE AND THE COLUMN TABLES ARE ALREADY LOADED.
    THE AMOUNT TABLE NEEDS TO BE LOADED WITH DATA COMMING FROM A TABLE CALLED LOAD_DATA.

    LOAD_DATA TABLE

    COL_1 COL_2 COL_3 COL_3

    17 151 640 48
    20 138 450 29
    58 345 920 109

    THE LOAD DATA IS MADE UP OF MULTIPLE COLUMNS LABELED COL_1, COL_2 AND ON.
    THE DATA IN THOSE COLUMNS IS TO BE LOADED IN THE AMOUNT TABLE LOOPING AND TAKING INTO ACCOUNT THAT FOR EACH VALUE COMMING FROM THE LOAD_DATA TABLE COL_1, ROW 1 RELATES TO TABLE NAME, NAME_ID AND TABLE COLUMN, COLUMN_ID TO MAKE AN INSERT STATEMENT:

    INSERT INTO AMOUNT VALUES (17,1,1)
    INSERT INTO AMOUNT VALUES (20,1,2)
    INSERT INTO AMOUNT VALUES (58,1,3)
    .
    .
    .

    INSERT INTO AMOUNT VALUES (640,3,3)
    INSERT INTO AMOUNT VALUES (450,3,4)
    .
    .
    .

  14. #14
    Join Date
    Jul 2003
    Posts
    2,296
    no problem.
    please describe all 4 tables.

    here is where I am slightly confused:

    THE LOAD DATA IS MADE UP OF MULTIPLE COLUMNS LABELED COL_1, COL_2 AND ON.
    THE DATA IN THOSE COLUMNS IS TO BE LOADED IN THE AMOUNT TABLE LOOPING AND TAKING INTO ACCOUNT THAT FOR EACH VALUE COMMING FROM THE LOAD_DATA TABLE COL_1, ROW 1 RELATES TO TABLE NAME, NAME_ID AND TABLE COLUMN, COLUMN_ID TO MAKE AN INSERT STATEMENT:


    Take your your data for example:
    Why is '640' have a name_id and column_id of '3','3'??
    Wouldn't it be 640, 3, 1 ????

    WAIT! I just looked at your first post!
    Doh!
    I assume you have the NAME column in your DATA LOAD:

    PHP Code:
    declare

    vName     varchar2(50);
    vId        varchar2(10);

    cursor cGetData is
        select name
    col_1col_2col_3col_4
        from load_data
    ;
        
    cursor cGetNameId is
        select name_id
        from name
        where name 
    vName;

    begin

        
    for vData in cGetData loop
        
            vName 
    := vData.name;
            
            
    open cGetNameId;
                
    fetch cGetNameId into vId;
                
    /* might want to add a check here to verify you got a name_id */
            
    close cGetNameId;
            
            
    insert into amount (amountname_idcolumn_id)
            
    values (
                
    vData.col1,
                
    vId,
                
    '1');
                
            
    insert into amount (amountname_idcolumn_id)
            
    values (
                
    vData.col2,
                
    vId,
                
    '2');
                
            
    insert into amount (amountname_idcolumn_id)
            
    values (
                
    vData.col3,
                
    vId,
                
    '3');

            
    insert into amount (amountname_idcolumn_id)
            
    values (
                
    vData.col4,
                
    vId,
                
    '4');
                
        
    end loop;

    end;

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  15. #15
    Join Date
    Mar 2003
    Posts
    45
    THIS IS WHAT I MEAND

    TABLES

    NAME

    |NAME_ID | NAME |
    |---------|--------|
    |1 |NEW YORK|
    |2 |SAN FRAN|
    .
    .
    .
    .

    COLUMN TABLE

    |COLUMN_ID | COLUMN_NAME|
    |---------- |------------|
    |1 |BUIL_1 |
    |2 | BUIL_2 |

    .
    .
    .
    LOAD_DATA

    |COL_1|COL_2|COL_3|COL_3|
    |-------|--------|-------|------|
    |17 |151 |640 |48 |
    |20 |138 |450 |29 |
    |58 |345 |920 |109 |



    TABLE TO BE POPULATED WITH DATA

    AMOUN TABLE

    |AMOUNT | NAME_ID | COLUMN_ID |
    |-------|----------|-----------|
    |17 |1 |1 |
    |20 |1 |2 |
    |58 |1 |3 |
    |151 |2 |1 |
    |138 |2 |2 |
    |345 |2 |3 |
    |640 |3 |1 |
    |450 |3 |2 |
    |920 |3 |3 |

Posting Permissions

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