-- This is an example of using external table in place of SQL*LOADER to -- eliminate the need of using temporary staging table and improve performance. -- Pipelined function is used to transform data in the external table before -- the data is "merged" into destination table. SQL> SQL> select * from emp_tmp; EMP_ID ENAME JOB MGR_ID HIREDATE SALARY ---------- ------------ ------------ ---------- --------------- ---------- COMM DEPT_ID ---------- ---------- 7499 ALLEN SALESMAN 7698 200281 00:00:00 300 300 30 7521 WARD SALESMAN 7698 220281 00:00:00 540 500 30 7654 MARTIN SALESMAN 7698 280981 00:00:00 1500 1400 30 EMP_ID ENAME JOB MGR_ID HIREDATE SALARY ---------- ------------ ------------ ---------- --------------- ---------- COMM DEPT_ID ---------- ---------- 7788 SCOTT ANALYST 7566 190487 00:00:00 3000 20 7839 KING PRESIDENT 171181 00:00:00 5000 10 7844 TURNER SALESMAN 7698 080981 00:00:00 1500 0 30 EMP_ID ENAME JOB MGR_ID HIREDATE SALARY ---------- ------------ ------------ ---------- --------------- ---------- COMM DEPT_ID ---------- ---------- 7902 FORD ANALYST 7566 031281 00:00:00 3000 20 1002 DAVID ANALYST 1001 010101 00:00:00 6000 1000 10 777 jusung Musician 999 010103 00:00:00 10000 556 10 EMP_ID ENAME JOB MGR_ID HIREDATE SALARY ---------- ------------ ------------ ---------- --------------- ---------- COMM DEPT_ID ---------- ---------- 7698 BLAKE MANAGER 7839 010581 00:00:00 1860 0 30 7369 SMITH CLERK 7902 171280 00:00:00 120 0 20 11 rows selected. SQL> SQL> create or replace directory admin_dat_dir as 'd:\yang\oracle\ExternalTables'; Directory created. SQL> / Directory created. SQL> drop table emp_ext; Table dropped. SQL> drop function transform; Function dropped. SQL> drop type table_emp_type; Type dropped. SQL> SQL> create table emp_ext 2 (emp_id number(4), ename varchar2(12), 3 job varchar2(12) , mgr_id number(4) , 4 hiredate date, salary number(8), comm number(8), 5 dept_id number(2)) 6 organization external 7 (type oracle_loader 8 default directory admin_dat_dir 9 access parameters (records delimited by newline 10 fields terminated by ',') 11 location ('emp1.txt','emp2.txt')); Table created. SQL> SQL> select * from emp_ext; EMP_ID ENAME JOB MGR_ID HIREDATE SALARY ---------- ------------ ------------ ---------- --------------- ---------- COMM DEPT_ID ---------- ---------- 7369 SMITH CLERK 7902 171280 00:00:00 100 0 20 7499 ALLEN SALESMAN 7698 200281 00:00:00 250 0 30 7521 WARD SALESMAN 7698 220281 00:00:00 450 0 30 EMP_ID ENAME JOB MGR_ID HIREDATE SALARY ---------- ------------ ------------ ---------- --------------- ---------- COMM DEPT_ID ---------- ---------- 7566 JONES MANAGER 7839 020481 00:00:00 1150 0 20 7654 MARTIN SALESMAN 7698 280981 00:00:00 1250 0 30 7698 BLAKE MANAGER 7839 010581 00:00:00 1550 0 30 EMP_ID ENAME JOB MGR_ID HIREDATE SALARY ---------- ------------ ------------ ---------- --------------- ---------- COMM DEPT_ID ---------- ---------- 7934 MILLER CLERK 7782 230182 00:00:00 3500 0 10 7 rows selected. SQL> SQL> create or replace type rec_emp_type is object 2 ( 3 emp_id number(4), 4 ename varchar2(12) , 5 job varchar2(12) , 6 mgr_id number(4) , 7 hiredate date, 8 salary number(8), 9 comm number(8), 10 dept_id number(2) 11 ); 12 / Type created. SQL> create or replace type table_emp_type is table of rec_emp_type; 2 / Type created. SQL> create or replace function transform 2 return table_emp_type PIPELINED is 3 cursor w_cur is select * from emp_ext; 4 out_rec rec_emp_type:=rec_emp_type(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); 5 begin 6 for rec in w_cur loop 7 rec.salary := rec.salary*1.2; 8 out_rec.emp_id := rec.emp_id; 9 out_rec.ename := rec.ename; 10 out_rec.job := rec.job; 11 out_rec.mgr_id := rec.mgr_id; 12 out_rec.hiredate := rec.hiredate; 13 out_rec.salary := rec.salary; 14 out_rec.comm := rec.comm; 15 out_rec.dept_id := rec.dept_id; 16 pipe row (out_rec); 17 end loop; 18 return; 19 end; 20 / Function created. SQL> MERGE INTO emp_tmp i 2 USING (select * from table(transform)) t 3 ON (i.emp_id = t.emp_id) 4 WHEN MATCHED THEN 5 UPDATE SET i.salary = t.salary 6 WHEN NOT MATCHED THEN 7 INSERT (emp_id, ename , job , mgr_id, hiredate, 8 salary , comm , dept_id) 9 VALUES ( t.emp_id, t.ename , t.job , t.mgr_id, t.hiredate , 10 t.salary , t.comm , t.dept_id ); 7 rows merged. SQL> SQL> select * from emp_tmp; EMP_ID ENAME JOB MGR_ID HIREDATE SALARY ---------- ------------ ------------ ---------- --------------- ---------- COMM DEPT_ID ---------- ---------- 7499 ALLEN SALESMAN 7698 200281 00:00:00 300 300 30 7521 WARD SALESMAN 7698 220281 00:00:00 540 500 30 7654 MARTIN SALESMAN 7698 280981 00:00:00 1500 1400 30 EMP_ID ENAME JOB MGR_ID HIREDATE SALARY ---------- ------------ ------------ ---------- --------------- ---------- COMM DEPT_ID ---------- ---------- 7788 SCOTT ANALYST 7566 190487 00:00:00 3000 20 7839 KING PRESIDENT 171181 00:00:00 5000 10 7844 TURNER SALESMAN 7698 080981 00:00:00 1500 0 30 EMP_ID ENAME JOB MGR_ID HIREDATE SALARY ---------- ------------ ------------ ---------- --------------- ---------- COMM DEPT_ID ---------- ---------- 7902 FORD ANALYST 7566 031281 00:00:00 3000 20 1002 DAVID ANALYST 1001 010101 00:00:00 6000 1000 10 777 jusung Musician 999 010103 00:00:00 10000 556 10 EMP_ID ENAME JOB MGR_ID HIREDATE SALARY ---------- ------------ ------------ ---------- --------------- ---------- COMM DEPT_ID ---------- ---------- 7698 BLAKE MANAGER 7839 010581 00:00:00 1860 0 30 7566 JONES MANAGER 7839 020481 00:00:00 1380 0 20 7934 MILLER CLERK 7782 230182 00:00:00 4200 0 10 EMP_ID ENAME JOB MGR_ID HIREDATE SALARY ---------- ------------ ------------ ---------- --------------- ---------- COMM DEPT_ID ---------- ---------- 7369 SMITH CLERK 7902 171280 00:00:00 120 0 20 13 rows selected. SQL> SQL> spool off