EXTERNAL TABLE (sql loader)

Le tabelle esterne sono tabelle di sola lettura e pertanto non possono essere eseguite DML e creati Indici e Constraint.
I metadati della tabella sono memorizzati all'interno del database mentre i dati sono memorizzati in file al di fuori del db.
E' possibile usare le utility SQL LOADER e ORACLE DATA PUMP per leggere le tabelle e caricarle nel db.

Per creare una tabella esterna occorre:

1. Creare una directory, in cui salvare il file esterno, all'interno del file system del server Oracle;
2. Creare l'oggetto oracle del database "directory";
3. Dare le grant di lettura e\o scrittura della directory agli utenti che devono utilizzare la tabella esterna;
4. Creare la tabella esterna.

S i n t a s s i
CREATE OR REPLACE DIRECTORY nome_directory AS riferimento_directory;

"riferimento_directory" è una stringa racchiusa tra apici come ad esempio 'C:\file_ester\estr'.
Non viene rilevato un errore in fase di creazione dell'oggetto Directory se la subdirectory non esiste, ma solo quando si crea la tabella esterna.

GRANT READ|WRITE ON DIRECTORY nome_directory TO utente;

CREATE TABLE nome_tabella_esterna
(colonna#1 tipo_dato,....colonna#n tipo_dato)
ORGANIZATION EXTERNAL
(TYPE ORACLE LOADER
DEFAULT DIRECTORY nome_directory
ACCESS PARAMETERS (
                                            RECORDS DELIMITED BY NEWLINE
                                            SKIP n
                                            FIELDS (colonna#1 tipo_dato,....colonna#n tipo_dato)
                                            )
LOCATION ( 'nome_file' )
);

RECORDS DELIMITED BY NEWLINE: ogni nuova riga del file corrisponde ad una nuovo record della tabella.
 SKIP n:   dice a ORACLE_LOADER che le prime n righe del file devono essere trascurate perché sono l'intestazione.
 FIELDS: specifica la lunghezza di ogni colonna della tabella che deve corrispondere esattamente alle colonne del file esterno.


Esempio:
CREATE DIRECTORY files_esterno_dir AS 'D:\U01\APP\ORACLE\ORADATA\ORCL';

Salvo il file esterno, tabella_esterna_prova.txt, nella directory di sopra.

pippo pluto paperino
--- ------------ -----
01 18/08/15 abc
02 18/08/15 def
03 18/08/15 ghi

CREATE TABLE TABELLA_EXTERNAL_PROVA
 ( pippo CHAR(2),
  pluto CHAR(9),
  paperino CHAR(3)
 )
 ORGANIZATION EXTERNAL
 (TYPE ORACLE_LOADER
 DEFAULT DIRECTORY files_esterno_dir
 ACCESS PARAMETERS
 (RECORDS DELIMITED BY NEWLINE
 SKIP 2  
 FIELDS
 ( pippo CHAR(4),
  pluto CHAR(5),
  paperino CHAR(6)
 )
 LOCATION ('tabella_esterna_prova.txt')
 );

Se si esegue la seguente query si verifica che è stata creata la  tabella con i dati presenti nel file.
  select * from TABELLA_EXTERNAL_PROVA;

R e g o l e

• non può contenere una colonna di tipo LOB, CLOB, BLOB;
• non può essere aggiunto una Constraint;
• non può essere impostato ad UNUSED una colonna;
• non possono essere eseguite UPDATE, DELETE e INSERT;
• possono essere applicate sulle colonne funzioni Conversione e Formattazione Dati;