drop TABLE EMP_ET;
drop table ls;

set echo on
clear screen
CREATE or replace DIRECTORY load_dir 
AS '/mnt/hgfs/docs/Presentations/Seminar/11gr2'
/
CREATE or replace DIRECTORY exec_dir 
AS '/mnt/hgfs/docs/Presentations/Seminar/11gr2'
/
pause

clear screen
CREATE TABLE EMP_ET
(
  "EMPNO" NUMBER(4),
  "ENAME" VARCHAR2(10),
  "JOB" VARCHAR2(9),
  "MGR" NUMBER(4),
  "HIREDATE" DATE,
  "SAL" NUMBER(7,2),
  "COMM" NUMBER(7,2),
  "DEPTNO" NUMBER(2)
)
ORGANIZATION external 
( TYPE oracle_loader
  DEFAULT DIRECTORY load_dir
  ACCESS PARAMETERS 
  ( RECORDS DELIMITED BY NEWLINE 
	preprocessor  exec_dir:'run_gunzip.sh'
    FIELDS TERMINATED BY "|" LDRTRIM 
  )
  location ( 'emp.dat.gz')
)
/
pause

clear screen
!file emp.dat.gz
!cat run_gunzip.sh
select empno, ename from emp_et where rownum <= 5;
pause

clear screen

CREATE TABLE ls
(
  line varchar2(255)
)
ORGANIZATION external 
( TYPE oracle_loader
  DEFAULT DIRECTORY load_dir
  ACCESS PARAMETERS 
  ( RECORDS DELIMITED BY NEWLINE 
	preprocessor  exec_dir:'run_ls.sh'
    FIELDS TERMINATED BY "|" LDRTRIM 
  )
  location ( 'run_ls.sh')
)
/
pause

clear screen
select * from ls;
pause
