Skip to Main Content
  • Questions
  • Import data from Flat File to two different Table using UTL_FILE.

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Nandan.

Asked: November 19, 2017 - 7:12 pm UTC

Last updated: November 20, 2017 - 4:30 pm UTC

Version: 11G

Viewed 1000+ times

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

Comments

A reader, November 21, 2017 - 10:29 am UTC


More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.