Re: Advanced SQL*Loader question

A copy of this was sent to ajayanand_at_my-dejanews.com (if that email address didn't require changing) On Tue, 22 Sep 1998 15:22:49 GMT, you wrote:

>I am trying to insert 2 records into a table using one record from the input
>file.
>
>The table has fields (acct_name, val1,val2) and the input file has
>(acct_code,v1a,v2a,v1b,v2b). I do a lookup for acct_name in "account_master"
>table using a function in the control file and insert acct_name into the
>table.
>
>Now it would insert (function(acct_code),v1a,v2a) first and then (function
>(acct_code),v1b,v2b).
>
>I have millions of records in account_master so I want to avoid the lookup 2

well, if account_master is indexed and you are using indexes, it will not matter if account_master has 1,000 or 1,000,000 records -- the index will take care of that.

how are you splitting the record up? by using >1 into clause (no control file for us to review). If so, since loader will save up records and array insert them -- you would need to call the function 2x just to make sure right data got into the right fields.

Another way would be to load (acct_code,v1a,v2a, v1b, v2b ) into a 'temp' table with a pair of triggers that looked like:

create trigger
after insert on temp_table
for each row
declare

   the_value varchar2(25);
begin

   the_value := function(:new.acct_code);    insert into t1 values (the_value,:new.v1a, :new.v2a );    insert into t2 values (the_value,:new.v1b, :new.v1b ); end;

create trigger
after insert on temp_table
begin

   delete from temp_table;
end;

>times. Since it is basically the same value for both records , Is there a way
>to store this value and resuse it than doing a select again ???
>
>Thanks in advance.
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.