Home » RDBMS Server » Server Utilities » Updates from flat file using SQL Loader
Updates from flat file using SQL Loader [message #69353] Fri, 14 December 2001 07:17 Go to next message
Ashish K. Mishra
Messages: 5
Registered: December 2001
Junior Member
Can I update the records in a table when I am having bulk of updated records in the flat file using SQL Loader.

----------------------------------------------------------------------
Re: Updates from flat file using SQL Loader [message #69354 is a reply to message #69353] Fri, 14 December 2001 07:31 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
no, you can't update records using sqlloader.
load flat file contents into interface table and process that table data using pl/sql

----------------------------------------------------------------------
Re: Updates from flat file using SQL Loader [message #69359 is a reply to message #69354] Fri, 14 December 2001 08:49 Go to previous messageGo to next message
Ashish K. Mishra
Messages: 5
Registered: December 2001
Junior Member
Is there no work around for this. Due to space crunch I am not in a position to create the table.

----------------------------------------------------------------------
Re: Updates from flat file using SQL Loader [message #69362 is a reply to message #69359] Fri, 14 December 2001 13:28 Go to previous messageGo to next message
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 Go to previous message
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.

----------------------------------------------------------------------
Previous Topic: Re: RequireOCP Exam Papers
Next Topic: Re: FREE OCP PAPERS
Goto Forum:
  


Current Time: Fri Apr 19 12:53:58 CDT 2024