Listar arquivos de uma pasta local através de uma consulta.

[fsitjaParticipante]

Tem uma outra ideia do Tom Kyte que ele exibiu durante o GUOB Tech Day que serve esse propósito também, mas é recurso do 11g Release 2. Segue abaixo o exemplo exibido pelo Tom, extraído do material dele:

[quote=”Tom Kyte”:witlemyw]

External Tables can run code now

• External tables allow for a preprocessor
  =>Program is run when you SELECT from external table
  => The ‘location’ is passed to the script/executable
  =>The executable does whatever it wants and writes to stdout
  =>Stdout is treated as the input file

• We need a way to control who can do what
  GRANT EXECUTE ON DIRECTORY handles that
  [/quote]


ops$tkyte%ORA11GR2> CREATE or replace DIRECTORY load_dir
2 AS '/mnt/hgfs/docs/Presentations/Seminar/11gr2'
3 /

Directory created.

ops$tkyte%ORA11GR2> CREATE or replace DIRECTORY exec_dir
2 AS '/mnt/hgfs/docs/Presentations/Seminar/11gr2'
3 /

Directory created.

ops$tkyte%ORA11GR2> CREATE TABLE EMP_ET
2 (
3 "EMPNO" NUMBER(4),
4 "ENAME" VARCHAR2(10),
5 "JOB" VARCHAR2(9),
6 "MGR" NUMBER(4),
7 "HIREDATE" DATE,
8 "SAL" NUMBER(7,2),
9 "COMM" NUMBER(7,2),
10 "DEPTNO" NUMBER(2)
11 )
12 ORGANIZATION external
13 ( TYPE oracle_loader
14 DEFAULT DIRECTORY load_dir
15 ACCESS PARAMETERS
16 ( RECORDS DELIMITED BY NEWLINE
17 preprocessor exec_dir:'run_gunzip.sh'
18 FIELDS TERMINATED BY "|" LDRTRIM
19 )
20 location ( 'emp.dat.gz')
21 )
22 /
Table created.

ops$tkyte%ORA11GR2> !file emp.dat.gz
emp.dat.gz: gzip compressed data, was "emp.dat", from Unix, last modified: Wed Oct 7 12:48:53 2009

ops$tkyte%ORA11GR2> !cat run_gunzip.sh
#!/bin/bash

/usr/bin/gunzip -c $*

ops$tkyte%ORA11GR2> select empno, ename from emp_et where rownum <= 5;

 EMPNO ENAME

---

  7369 SMITH
  7499 ALLEN
  7521 WARD
  7566 JONES
  7654 MARTIN

E o pulo do gato:

ops$tkyte%ORA11GR2> CREATE TABLE ls
2 (
3 line varchar2(255)
4 )
5 ORGANIZATION external
6 ( TYPE oracle_loader
7 DEFAULT DIRECTORY load_dir
8 ACCESS PARAMETERS
9 ( RECORDS DELIMITED BY NEWLINE
10 preprocessor exec_dir:'run_ls.sh'
11 FIELDS TERMINATED BY "|" LDRTRIM
12 )
13 location ( 'run_ls.sh')
14 )
15 /

Table created.

ops$tkyte%ORA11GR2> select * from ls;

LINE

11 things about 11gr2.ppt
diyp.sql
ebr.old.sql
ebr.sql
emp.ctl
emp.dat.gz
EMP_ET_26122.log
emp_et.sql
LS_26122.log
run_gunzip.sh
run_ls.sh

11 rows selected.