|
|
|
Re: Updates from flat file using SQL Loader [message #69362 is a reply to message #69359] |
Fri, 14 December 2001 13:28 |
Suresh Vemulapalli
Messages: 624 Registered: August 2000
|
Senior Member |
|
|
there is a way to do that (normally i dont prefer this method, i would rather
go for other method which i mentioned in my earlier message),
but it will be slow when compared to normal sqlloader operation.
look at following example, so that you will have some idea.if you want information about
records updated, write extra code in function, dont look at
sqlloader log file for that information.
table structure:
EMPNO NOT NULL NUMBER
ENAME VARCHAR2(10)
SAL NUMBER
EMPNO is primary key
control file :
LOAD DATA
INFILE 'emp1.txt'
APPEND
INTO TABLE emp2
(empno position(01:05) "update_emp(:empno,:ename,:sal)",
ename position(07:15) ,
sal position(17:20))
create function like below:
create or replace function update_emp(p_empno number,
p_ename varchar2,
p_sal number) return number IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_count number;
begin
select count(*) into l_count
from emp2
where empno=p_empno;
if l_count>0 then
update emp2
set ename=p_ename,
sal=p_sal
where empno=p_empno;
end if;
commit;
return p_empno;
end;
my data file (1st time)
12345 suresh 3100
32456 john king 5100
12344 robert 6601
12223 rob 1434
table's data after first load:
SQL> select * from emp2;
EMPNO ENAME SAL
---------- ---------- ----------
12345 suresh 3100
32456 john king 5100
12344 robert 6601
12223 rob 1434
my data file (second time)
12345 suresh1 4500
32456 john 7777
12344 robert 6688
12223 rob1 1230
12222 kris 3455
23223 kkkk 7266
table data aftr second load
SQL> select * from emp2;
EMPNO ENAME SAL
---------- ---------- ----------
12345 suresh1 4500
32456 john 7777
12344 robert 6688
12223 rob1 1230
12222 kris 3455
23223 kkkk 7266
6 rows selected.
HTH
Suresh Vemulapalli
----------------------------------------------------------------------
|
|
|
Re: Updates from flat file using SQL Loader [message #69385 is a reply to message #69354] |
Fri, 21 December 2001 05:25 |
Ashish K. Mishra
Messages: 5 Registered: December 2001
|
Junior Member |
|
|
I tried creating the function but it is not working. Actually when u are using some user defined stand alone function in SQL or PL/SQL then it must guarantee that it is not going to update the database. If it is not stand alone and it is packaged then it should be WNDS.
Also on Oracle 8.0.5 the pragma autonomous_transaction is giving error. I tried by the execution without the pragma. So how should I proceed now.
----------------------------------------------------------------------
|
|
|