DBMS_DATAPUMP.OPEN ORA-31626 : job does not exist

Posted on mars 6th, 2009 in Oracle, Tips by David

Oracle : 10g

Environnement : HPUX ( mais peut arriver ailleurs je pense ).

Symptomes :

Dans mon bloc PL/SQL j’ai ces ligne :


h1 := dbms_datapump.open (operation => ‘EXPORT’, job_mode => ‘SCHEMA’, job_name => ‘EXPORT’ , version => ‘COMPATIBLE’);

dbms_datapump.set_parallel(handle => h1, degree => 1);

dbms_datapump.add_file(handle => h1, filename => ‘EXPDAT.LOG’, directory => ‘DATA_PUMP_DIR’, filetype => 3);

dbms_datapump.metadata_filter(handle => h1, name => ‘SCHEMA_EXPR’, value => ‘IN(’‘TOTO’‘,’TOTO2‘‘)’);

dbms_datapump.add_file(handle => h1, filename => ‘EXPDAT%U.DMP’, directory => ‘DATA_PUMP_DIR’, filetype => 1);

dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);

dbms_datapump.detach(handle => h1);

Et ca me donne ceci :

ORA-12012: error on auto execute of job 2345167
ORA-31626: job does not exist

Frustrant non ? :)

La chose a savoir : les commandes datapump on besoin d’être dans leur propre transaction … c’est bête mais c’est comme ca !

La solution :

begin
h1 := dbms_datapump.open (operation => ‘EXPORT’, job_mode => ‘SCHEMA’, job_name => ‘EXPORT’ , version => ‘COMPATIBLE’);
end;

begin
dbms_datapump.set_parallel(handle => h1, degree => 1);
end;

begin
dbms_datapump.add_file(handle => h1, filename => ‘EXPDAT.LOG’, directory => ‘DATA_PUMP_DIR’, filetype => 3);
end;
dbms_datapump.metadata_filter(handle => h1, name => ‘SCHEMA_EXPR’, value => ‘IN(’‘TOTO’‘,”TOTO2‘‘)’);
begin
dbms_datapump.add_file(handle => h1, filename => ‘EXPDAT%U.DMP’, directory => ‘DATA_PUMP_DIR’, filetype => 1);
end;
dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
begin
dbms_datapump.detach(handle => h1);
end;

Pour les fanas de lecture Doc ID n° 459419.1

a+