Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2013
    Posts
    2

    Post Unanswered: SQL*Loader table updation

    Hi All,

    I am new to the sql loader utility and it will be of great help if anyone throw light on the below issue.

    I have a huge edifact file which needs to be loaded into oracle.
    My control file looks like :

    LOAD DATA
    TRUNCATE INTO TABLE passenger_hist
    TRAILING NULLCOLS
    (
    --from the input file---
    SEG_ID position(1:3),
    SEG_VLU position(5) CHAR(23000) TERMINATED BY '\n',
    ROW_NO SEQUENCE(MAX,1),
    load_tm "sysdate"
    )

    On average the table will get 400K rows.
    My problem is, i need create few more columns to this passenger_hist table and those columns has to be updated based on the values (mainly Seg_id) that i'm going to loaded through SQL loader.

    I tried using the trigger but it poses performance issues. I create a package to update those columns using array but still very slow.

    Is there any way to assign values to the new columns (apart from mentioned above) based on the seg_id and seg_vlu thru function or any arrays etc as part of sql loader ?

    Pls shout if you need more details if my question is too vague.

    Many thanks in advance,
    Venkatesh M

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What would you put into these columns? You said that their values depend on SEG_ID. How? You can use functions in SQL*Loader. Here are just a few examples I collected from some of my control files (meaningless without tables' descriptions, input data etc.; I just meant to give you an idea):
    Code:
    sifra_mm       "lpad(:sifra_mm, 7, '0')",
    date_loaded    "trunc(sysdate)",
    ename          "substr(:file_name, 
                          instr(:file_name, '\\', -1) + 1, 
                          instr(:file_name, '.', -1) - instr(:file_name, '\\', -1) - 1
                        )",
    transaction_id " 'auto-' || seqa.nextval"

  3. #3
    Join Date
    Jun 2013
    Posts
    2
    Many thanks for ur reply Littlefoot.

    Here is a scribble from the logic to update the remaining columns.
    The pl/sql package will fetch the records from passenger_hist thru a cursor
    and assign the variables and update the columns.

    cursor c1 is select * from passenger_hist order by row_no asc;
    TYPE my_type IS TABLE OF passenger_hist%ROWTYPE INDEX BY PLS_INTEGER;
    Row_Val my_type;
    curr_grp_no number;
    begin

    for i in 1..row_val.count loop

    if row_val(i).seg_id = 'UNH' or row_val(i).seg_id = 'UNT'
    Then curr_grp_no := 1;
    l_key := Cust_Id_Seq.Nextval;

    elsif curr_grp_no <=4 and row_val(i).seg_id = 'APL'
    then curr_grp_no := 2;
    v_flag_gr2_sdi :='N';

    . .
    . . .
    . . . .

    From the above code we can see that i'm fetching all the records one by one in order to update every record and assigning some variables like l_key etc.. which will be used to update the respective column..
    I doubt whether i can use this complex logic in sql loader.

    And the requirement don't stop here. Updating the other columns is the first step and next task is to do string manipulation against the Seg_vlu (clob column) which has a string with two different delimiters (one is to split column wise and another one to split the records row wise).

    Combining all these requirements along with the loads of records makes the pl/sql package very slow..

    Did you get a high level picture of the above issue..

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    row by row is slow by slow; as you have discovered.
    PL/SQL will NEVER be faster than plain SQL.
    If/when the data file to be "loaded" resides on the DB Server, then EXTERNAL TABLE can be used.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Due to the fact you are loading the table as "truncate", the following would never execute because curr_grp_no column is NULL:
    Quote Originally Posted by venkatesh8484 View Post
    . . . E t c . . .
    elsif curr_grp_no <=4 and row_val(i).seg_id = 'APL'
    then curr_grp_no := 2;
    v_flag_gr2_sdi :='N';
    . . . .
    Therefore, if you decide not to use external table, perhaps you could try something like this (not tested):
    Code:
    LOAD DATA
    TRUNCATE INTO TABLE passenger_hist
    TRAILING NULLCOLS
    (
    SEG_ID position(1:3),
    SEG_VLU position(5) CHAR(23000) TERMINATED BY '\n',
    ROW_NO SEQUENCE(MAX,1),
    LOAD_TM "SYSDATE",
    curr_grp_no 
      EXPRESSION "CASE WHEN :SEG_ID IN ('UNH','UNT') THEN 1 
                       WHEN :SEG_ID IN ('APL') THEN 2 END",
    l_key 
      EXPRESSION "CASE WHEN :SEG_ID IN ('UNH','UNT') THEN Cust_Id_Seq.Nextval END", 
    v_flag_gr2_sdi :='N';
      EXPRESSION "CASE WHEN :SEG_ID IN ('APL') THEN 'N' END"
    )
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Tags for this Thread

Posting Permissions

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