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