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>
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.
Excelente exemplo de external tables.
E esta nomenclatura de tabela é familiar para quem trabalha com ERP BAAN ou LN.
Abs,
Thiago