into table TABLE_D
WHEN P_TYPERECORD = 'DD'
TRAILING NULLCOLS
(
TABLE_D_ID expression "SQ_TABLE_D.NEXTVAL",
TABLE_H_ID expression "SQ_TABLE_H.CURRVAL",
P_TYPERECORD POSITION(1:2),
P_DESC POSITION(3:10),
The most important about this question is how to maitain relationship across tables using constraints and sequences, while loading from external tables.let's start with this data.
DACCOUNTING ,NEW YORK
ECLARK ,MANAGER
EMILLER ,CLERK
EKING ,PRESIDENT
DRESEARCH ,DALLAS
ESMITH ,CLERK
EFORD ,ANALYST
EADAMS ,CLERK
ESCOTT ,ANALYST
EJONES ,MANAGER
DSALES ,CHICAGO
EALLEN ,SALESMAN
EJAMES ,CLERK
ETURNER ,SALESMAN
EBLAKE ,MANAGER
EMARTIN ,SALESMAN
EWARD ,SALESMAN
DOPERATIONS ,BOSTON
create table DEPT(
deptno number ,
dname varchar2(20),
location varchar2(20),
constraint dept_pk primary key(deptno) );
create table EMP(
empno number,
ename varchar2(25),
deptno number,
constraint emp_pk primary key(empno) ,
constraint emp_fk foreign key(deptno)
references dept );
create sequence dept_seq;
create sequence emp_seq;
I have the above delimited data,
a) if the record starts with 'D' then it should go into DEPT table and have a Sequence generated PK.
b) using that sequence generated PK from DEPT table load all the record starts with 'E' into EMP table
so the final output after the load should be like this.
demo@ORA12C> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
1 ACCOUNTING NEW YORK
2 RESEARCH DALLAS
3 SALES CHICAGO
demo@ORA12C> select empno,ename,deptno from emp order by deptno;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7782 CLARK 1
7839 KING 1
7934 MILLER 1
7566 JONES 2
7902 FORD 2
7876 ADAMS 2
7369 SMITH 2
7788 SCOTT 2
7521 WARD 3
7844 TURNER 3
7499 ALLEN 3
7900 JAMES 3
7698 BLAKE 3
7654 MARTIN 3
14 rows selected.
demo@ORA12C>Using External Tables - it goes like this.
demo@ORA12C> CREATE TABLE ext_tbl
2 (
3 "X" VARCHAR2(20),
4 "Y" VARCHAR2(30)
5 )
6 ORGANIZATION external
7 (
8 TYPE oracle_loader
9 DEFAULT DIRECTORY TMP
10 ACCESS PARAMETERS
11 (
12 RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
13 BADFILE 'TMP':'ctl.bad'
14 LOGFILE 'log.txt_xt'
15 READSIZE 1048576
16 FIELDS TERMINATED BY "," LDRTRIM
17 REJECT ROWS WITH ALL NULL FIELDS
18 (
19 "X" CHAR(255)
20 TERMINATED BY ",",
21 "Y" CHAR(255)
22 TERMINATED BY ","
23 )
24 )
25 location
26 (
27 'data.dat'
28 )
29 )REJECT LIMIT UNLIMITED ;
Table EXT_TBL created.
demo@ORA12C> select * from ext_tbl;
X Y
-------------------- ------------------------------
DACCOUNTING NEW YORK
ECLARK MANAGER
EMILLER CLERK
EKING PRESIDENT
DRESEARCH DALLAS
ESMITH CLERK
EFORD ANALYST
EADAMS CLERK
ESCOTT ANALYST
EJONES MANAGER
DSALES CHICAGO
EALLEN SALESMAN
EJAMES CLERK
ETURNER SALESMAN
EBLAKE MANAGER
EMARTIN SALESMAN
EWARD SALESMAN
DOPERATIONS BOSTON
18 rows selected.
demo@ORA12C> alter table emp disable constraint emp_fk;
Table altered.
demo@ORA12C> alter table emp disable constraint emp_pk;
Table altered.
demo@ORA12C> alter table dept disable constraint dept_pk;
Table altered.
demo@ORA12C> insert all
2 when substr(x,1,1) ='D' then
3 into dept(deptno,dname,location) values(dept_seq.nextval,names#,values#)
4 when substr(x,1,1)='E' then
5 into emp(empno,ename,deptno) values(emp_seq.nextval,names#,dept_seq.currval)
6 select x, substr(x,2) names#, y values#
7 from ext_tbl;
18 rows created.
demo@ORA12C> select * from dept;
DEPTNO DNAME LOCATION
---------- -------------------- --------------------
1 ACCOUNTING NEW YORK
5 RESEARCH DALLAS
11 SALES CHICAGO
18 OPERATIONS BOSTON
demo@ORA12C> select * from emp ;
EMPNO ENAME DEPTNO
---------- ------------------------- ----------
2 CLARK 2
3 MILLER 3
4 KING 4
6 SMITH 6
7 FORD 7
8 ADAMS 8
9 SCOTT 9
10 JONES 10
12 ALLEN 12
13 JAMES 13
14 TURNER 14
15 BLAKE 15
16 MARTIN 16
17 WARD 17
14 rows selected.
demo@ORA12C> Questions:
1) How do we guarantee the query against the External table ( select * from external_table ) will read the data sequentially from the data file? if it reads the data randomly then the above requirement can be handled using External Tables?