Un exemple de script pour gérer et utiliser des tables externes Oracle (external tables)

On va charger emp a partir d’un fichier externe CSV, en utilisant SQL et les tables externes Oracle ( Oracle 9i, Oracle 10g et + si affinités )

D’abord, creer un repertoire oracle de travail (Oracle directory) associé au repertoire ou se trouve le fichier d’entree.
Pour ce faire, SCOTT doit avoir le privilège Systeme ‘CREATE ANY DIRECTORY’
…et le CREATE TABLE bien sur !

sous SQL>

CONNECT SYSTEM/xx@db
GRANT CREATE ANY DIRECTORY TO scott;
CONNECT scott/tiger@db
create or replace directory dossier_temp as ‘/tmp’;
drop table table_externe_emp;

rem : si le user qui lit/ecrit dans la directory n’est pas celui qui l’a créé
(ici c’est SCOTT qui fait tout), ce uyser aura aussi besoin des droitsd R/W sur la directory :

SQL> grant read,write on directory dossier_temp to scott;

Créer une ‘table’ externe attachee au fichier d’entree

create table table_externe_emp
(EMPNO NUMBER(4) ,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
)
ORGANIZATION EXTERNAL
( type oracle_loader
default directory dossier_temp
access parameters
( records delimited by newline
badfile ‘charge_emp.bad’
logfile ‘charge_emp.log’
fields terminated by ‘,’
(empno, ename, job, mgr, hiredate date, sal, comm, deptno)
)
location (’emp.csv’)
)
– attention c’est important sinon ca marche moins bien !
reject limit unlimited
/

Lire la table externe

select * from table_externe_emp;

!! Attention en cas de problème regardez les fichiers LOG et BAD sur le serveur

– lecture table externe et INSERTION dans une autre cible
– on insère dans la table destination si elle existe

insert into mon_emp
select * from table_externe_emp;
commit;

– lecture table externe et CREATION/INSERTION dans une autre cible
– on peut aussi pour aller + vite recopier la table externe ET son contenu !

create table mon_emp
as select * from table_externe_emp;