External Table - ORACLE_LOADER ORACLE_DATAPUMP
Una External Table è una tabella di sola lettura i cui metadata sono conservati nel database mentre i dati sono esterni al db.
Non si possono eseguire DML ne creare indici.
Puoi accedere ai dati in due modi: oracle_loader o oracle_datapump.Utilizziamo ORACLE_LOADER
1) Creare una directory fiscia e logica dove salvare il file esterno contente i dati.
Colleghiamoci al pluggable database
SQL > conn /as sysdba
SQL> alter session set container=orclpdb;
SQL> grant create any directory to hr;
Accediamo come hr e creaimo la directory su file system e sul db.
SQL> conn hr/hr@orclpdb
SQL> CREATE DIRECTORY EXT_DIR as '/u01/app/oracle/file_ext';
SQL> select * from ALL_DIRECTORIES where directory_name='EXT_DIR';
2) Creare il file contenente i dati separato da "," all'interno della directory "file_ext".
[oracle@test file_ext]$ vi ext_table.csv
1,spider,man
2,bat,man
3,super,man
4,x,men
3) Accedere come hr e creare la tabella che deve leggere i dati prensenti nel file.
SQL> CREATE TABLE EMP_LOAD_ext
(id NUMBER,
nome VARCHAR2(100),
cognome VARCHAR2(100)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXT_DIR
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
)
LOCATION ('ext_table.csv')
)
reject limit unlimited; ---> questo comando dice di caricare il file anche se ci sono errori nel caricamento
4) Verifica
SQL> select * from EMP_LOAD_ext
SQL> delete EMP_LOAD_EXT;
Errore SQL: ORA-30657: operation not supported on external organized table
30657.0000 - "operation not supported on external organized table"
*Cause: User attempted on operation on an external table which is not supported.
SQL> update EMP_LOAD_ext set nome='PETER' where id =1;
Errore SQL: ORA-30657: operation not supported on external organized table
30657.0000 - "operation not supported on external organized table"
*Cause: User attempted on operation on an external table which is not supported.
Se aggiungo una riga al file
[oracle@test file_ext]$ vi ext_table.csv
1,spider,man
2,bat,man
3,super,man
4,x,men
5,cat,woman
Eseguo la query e verifico che la tabella vede la nuova riga senza effettuare alcuna operazione:
SQL> select * from EMP_LOAD_ext;
Se sul server si apre il file di log "EMP_LOAD_EXT_29945.log" non compare alcun errore.
Se invece si modifica il file da carica mettendo un errore come segue:
1,spider,man
2,bat,man
3,super,man
4,x,men
5,cat,woman
xxx,she,hulk
La query non restituisce l'ultimo record e viene generato un file di errore
[oracle@test file_ext]$ more EMP_LOAD_EXT_29945.bad
xxx,she,hulk
Utilizziamo ORACLE_DATAPUMP
Esistono due metodi. Per entrambi eseguire il passo 1 dell'esempio precedente.
1) Il seguente comando crea un file .dmp dalla query sulla tabella employees e successivamente crea la tabella emp_dpump_ext che estrae i dati dal file dump stesso.
SQL> CREATE TABLE emp_dpump_ext
(employee_id,
first_name,
last_name
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY EXT_DIR
LOCATION ('EMP.dmp')
)
as
SELECT employee_id,first_name, last_name
FROM employees;
Nel filesystem viene creato il file EMP.dmp.
Se si cancella o si rinomina il file dump la query sulla tabella creata termina in errore:
SQL> select * from emp_dpump_ext;
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11010: unable to open at least one dump file for fetch
29913. 00000 - "error in executing %s callout"
*Cause: The execution of the specified callout caused an error.
*Action: Examine the error messages take appropriate action.
2) Creare una tabella esterna utilizzando un file dump esistente.
Supponiamo che nella direcotry EXT_DIR ci sia un file dump exp_EMPLOYEES.dmp estratto dalla query
SELECT employee_id,first_name, last_name
FROM employees;
sql> CREATE TABLE emp_dpump_ext_2
(EMPLOYEE_ID NUMBER ,
first_name VARCHAR2(100),
last_name VARCHAR2(100)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY EXT_DIR
LOCATION ('exp_EMPLOYEES.dmp')
);
