Exemplo de External Tables

External Tables são excelentes para ambientes DW, para armazenar dados temporários com a velocidade do SQL*Loader.
Recentemente, um amigo meu me pediu um exemplo de External Tables, e eu fiz este para ele. Vou então deixar aqui para referência.

Veja que eu crio o diretório, mas não movo o arquivo para lá no início (pois eu esqueci mesmo), e recebo o erro que o arquivo não foi encontrado. Ao mover o arquivo novamente para o diretório criado (external), posso executar SELECT diretamente no arquivo.

[oracle@OracleDG01 ~]$ mkdir external
 [oracle@OracleDG01 ~]$ ls -lh
 total 2.3G
 drwxr-xr-x 2 oracle oinstall 4.0K Jun 1 12:07 external
 -rw------- 1 oracle oinstall 1.3G May 31 07:33 p10098816_112020_LINUX_1of7.zip
 -rw------- 1 oracle oinstall 1002M May 31 07:37 p10098816_112020_LINUX_2of7.zip
 -rw-r--r-- 1 oracle oinstall 5.6K Jun 1 12:08 tccom001_10.csv
 [oracle@OracleDG01 ~]$ sqlplus / AS SYSDBA
 SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 1 12:08:33 2011
 Copyright (c) 1982, 2010, Oracle. All rights reserved.
 Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
 With the Partitioning, Oracle Label Security, OLAP, Data Mining,
 Oracle Database Vault and Real Application Testing options
 SQL> ALTER USER SCOTT ACCOUNT UNLOCK IDENTIFIED BY TIGER;
 User altered.
 SQL> CREATE DIRECTORY SCOTT_DIR AS '/home/oracle/external';
 Directory created.
 SQL> GRANT READ, WRITE ON DIRECTORY SCOTT_DIR TO SCOTT;
 Grant succeeded.
 SQL> CONN SCOTT/TIGER;
 Connected.
 SQL> CREATE TABLE tccom001 (emno NUMBER (5) NOT NULL, nama VARCHAR2(35) NULL, cwoc VARCHAR2(3) NULL);
 Table created.
 SQL> create table ext_tccom001 (
 emno Number,
 nama Varchar2(20),
 cwoc Varchar2(20)
 )
 organization external (
 type oracle_loader
 default directory SCOTT_DIR
 access parameters (
 records delimited by newline
 fields terminated by ';'
 missing field values are null
 )
 location ('tccom001_10.csv')
 )
 reject limit unlimited; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
 Table created.
 SQL> SELECT COUNT(*) FROM ext_tccom001;
 SELECT COUNT(*) FROM ext_tccom001
 *
 ERROR at line 1:
 ORA-29913: error in executing ODCIEXTTABLEOPEN callout
 ORA-29400: data cartridge error
 KUP-04040: file tccom001_10.csv in SCOTT_DIR not found
 SQL> !ls -lh
 total 2.3G
 drwxr-xr-x 2 oracle oinstall 4.0K Jun 1 12:19 external
 -rw------- 1 oracle oinstall 1.3G May 31 07:33 p10098816_112020_LINUX_1of7.zip
 -rw------- 1 oracle oinstall 1002M May 31 07:37 p10098816_112020_LINUX_2of7.zip
 -rw-r--r-- 1 oracle oinstall 5.6K Jun 1 12:08 tccom001_10.csv
 SQL> !mv tccom001_10.csv external
 SQL> /
 COUNT(*)
 ----------
 67
 SQL> SET LINES 210
 SQL> SET PAGES 1000
 SQL> SELECT * FROM ext_tccom001;
 EMNO NAMA CWOC
 ---------- -------------------- --------------------
 10 0 Nao definido
 10 1 Engenheiro de Vendas
 10 3 Projetista
 10 4 Planejista
 ...
 10 999999 "PLANEJAMENTO"
 67 rows selected.
 SQL>

Ricardo Portilho Proni

Com 20 anos de experiência profissional, Oracle ACE Member – eleito pela Oracle Corporation um dos maiores especialistas do mundo em Oracle Database- Trabalhou em grande parte dos maiores bancos de dados Oracle do Brasil. Certificado em Oracle, SQL Server, DB2, MySQL, Sybase e Websphere.

Conselheiro do GPO e do GUOB, palestrante do ENPO, GUOB Tech Day e Oracle Open World, escritor da Revista SQL Magazine e Instrutor na Nerv.