| Selective loading of data using SQL Loader [message #69325] |
Mon, 10 December 2001 15:29  |
Lynda
Messages: 2
Registered: December 2001
|
Junior Member |
|
|
Hi,
There are many examples of how to selectively load data from a flatfile into Oracle using SQL* Loader, however I am trying to find out if it is possible to only load records to one table, if a value in the new record to be uploaded exists in another table.
For example:
I have a flatfile that is to be loaded into Table1
The new record should only be loaded into Table1 if a value between certain character positions in the flatfile is contained in Table2.
So: If data value starting from char 10 and finishing at char 15 in a line in the flatfile exists in Field1 of Table2, then create new record with flatfile data in Table1.
It is a simple data validation check but on the other side, i.e. in Oracle as opposed to the flatfile. Does anybody know how to do this with SQL* Loader???
Thanks!
----------------------------------------------------------------------
|
|
|
|
| Re: Selective loading of data using SQL Loader [message #69333 is a reply to message #69325] |
Tue, 11 December 2001 05:05  |
Bala
Messages: 205
Registered: November 1999
|
Senior Member |
|
|
Hi
As far as i know there isn't any straight forward
method to do this.
Either you can load the data into a temporary table first and then write a sql statement to select the records and insert into the table1.
Or you can use oracle's utl_file utility to read the external file and bring each record into buffer.. and then you can check that value exists in table2..and decide whether to insert or discard that record..
In 9i there is new feature in which you can keep external flat file as a external table..
You can manipulate data in a flat file using sql queries same as a oracle table.
Selective loading will be a piece of cake in 9i.
----------------------------------------------------------------------
|
|
|
|
| Re: Selective loading of data using SQL Loader [message #69336 is a reply to message #69325] |
Tue, 11 December 2001 05:57 |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
there is some work around to do that, but that depends on how big your flat file is.
If it contains millions of records, as Bala said try loading data into interface table
and process using pl/sql.
Solution:
let's say we have 2 tables emp,emp100
Scenario:
I want to load data into emp table and at the same time i have to check in emp100 table for
valid empno.
emp100 data:
EMPNO ENAME SAL
---------- -------------------- ----------
32456 john king 4000
12344 robert 4400
my text file:(emp1.txt)
12345 suresh 2000
32456 john king 5000
12344 robert 6400
12223 rrr 1234
emp table data:
no data
Step 1: define not null constraint on empno of emp table
step 2: write function to look for valid empno in emp100 table
create or replace function check_empno(p_empno varchar2) return varchar2 is
l_count number;
r_empno varchar2(10);
begin
select count(*) into l_count from emp100 where empno=p_empno;
if l_count>0 then
r_empno:=p_empno;
else
r_empno:=null;
end if;
return r_empno;
end;
step 3: write control file like below
LOAD DATA
INFILE 'emp1.txt'
APPEND
INTO TABLE emp
(empno position(01:05) "check_empno(:empno)",
ename position(07:15),
sal position(17:20))
step 4: invoke sqlloader
look at emp table data
EMPNO ENAME SAL
---------- -------------------- ----------
32456 john king 5000
12344 robert 6400
Log file info:
Table EMP:
2 Rows successfully loaded.
2 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
sqlloader rejected 2 records (record 1 & 4 in flat file because those 2 empno are not exist in
emp100 table)
Note: you cant use direct load option when use stored functions in control file.
Dont go for this method if data file contains millions of records.
HTH
Suresh Vemulapalli
----------------------------------------------------------------------
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
