You Asked
Hi
Please help this Question.
Import data from Following Flat File to two different Table using UTL_FILE.
a. EMP and b. DEPT
Note
---
1. In Last Line NULL Employee Should not Entry into Table.
2. Deptno Should go to both the Table EMP as well as DEPT.
DEPTNO,DNAME,LOC,EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,SAL
10,ACCOUNTING,NEW YORK,7839,KING,PRESIDENT,17-NOV-81,,5000
10,ACCOUNTING,NEW YORK,7782,CLARK,MANAGER,7839,09-JUN-81,,2450
10,ACCOUNTING,NEW YORK,7934 MILLER,CLERK,7782,23-JAN-82,,1300
20,RESEARCH,DALLAS,7876,ADAMS,CLERK,7788,12-JAN-83,,1100
20,RESEARCH,DALLAS,7788,SCOTT,ANALYST,7566,09-DEC-82,,3000
20,RESEARCH,DALLAS,7369,SMITH,CLERK,7902,17-DEC-80,,800
20,RESEARCH,DALLAS,7902,FORD,ANALYST,7566,03-DEC-81,3000
20,RESEARCH,DALLAS,7566,JONES,MANAGER,7839,02-APR-81,2975
30,SALES,CHICAGO,7521,WARD,SALESMAN,7698,22-FEB-81,500,1250
30,SALES,CHICAGO,7900,JAMES,CLERK,7698,03-DEC-81,,950
30,SALES,CHICAGO,7499,ALLEN,SALESMAN,7698,20-FEB-81,300,1600
30,SALES,CHICAGO,7654,MARTIN,SALESMAN,7698,28-SEP-81,1400,1250
30,SALES,CHICAGO,7698,BLAKE,MANAGER,7839,01-MAY-81,2850
30,SALES,CHICAGO,7844,TURNER,SALESMAN,7698,08-SEP-81,0,1500
40,OPERATIONS,BOSTON
Thanks in Advance...
and Chris said...
Why the need to use utl_file?
An external table seems the better way to go here. With insert all, you can add all rows to both tables in one statement:
insert all
when empno is not null then into emp (...) values (...)
into dept (...) values (...)
select * from ext_tab;
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment