Bonjour,

Je tente désespérément de planifier un job DATAPUMP à l'aide de DBMS_DATAPUMP dans un bloc PL/SQL.

Jusqu'ici pas de souci, d'autant que Oracle le met à disposition directement dans l'aide :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
create or replace
PROCEDURE                                                                                                   pr_expdp_full AS
/**************************************************
Procedure permettant d'effectuer un export FULL
de l'instance avec la technologie DATAPUMP
 
Déclarer l'objet répertoire dans l'instance
CREATE DIRECTORY "DATAPUMP_DIR" AS '<Mon chemin réseau>'
 
**************************************************/
 
JobHandle      NUMBER; -- Data Pump job handle
JobStamp       VARCHAR2(13); -- Date time stamp
InstanceName   varchar2(30); -- Name of instance
ind NUMBER; -- Loop index
n_Exist NUMBER; -- count Job
JobHandle_Exist NUMBER; --JobHandle exist
percent_done NUMBER; -- Percentage of job complete
job_state VARCHAR2(30); -- To keep track of job state
le ku$_LogEntry; -- For WIP and error messages
js ku$_JobStatus; -- The job status from get_status
jd ku$_JobDesc; -- The job description from get_status
sts ku$_Status; -- The status object returned by get_status
 
BEGIN
 
-- TimeStamp File with system date
select to_char(SYSDATE,'DDMMRRRR-HH24MI') into JobStamp from dual ;
-- Instance Name to export
select rtrim(global_name) into InstanceName from global_name;
 
--Delete Job if exist
select count(*) into n_Exist from user_datapump_jobs where job_name = 'DAILY_EXPDP_'||InstanceName;
IF n_Exist > 0 
THEN  
  JobHandle_Exist := DBMS_DATAPUMP.ATTACH('DAILY_EXPDP_'||InstanceName,'SYSTEM');
  dbms_datapump.stop_job(JobHandle_Exist);
  DBMS_DATAPUMP.DETACH (JobHandle_Exist);
  execute immediate('DROP TABLE DAILY_EXPDP_'||InstanceName||'');
END IF;
 
-- Create a (user-named) Data Pump job to do a schema export.
JobHandle :=
DBMS_DATAPUMP.OPEN(
operation => 'EXPORT'
,job_mode => 'FULL'
,job_name => 'DAILY_EXPDP_'||InstanceName
,version => 'COMPATIBLE'
);
dbms_output.put_line('after OPEN');
-- Specify a single dump file for the job (using the handle just returned)
-- and a directory object, which must already be defined and accessible
-- to the user running this procedure.
DBMS_DATAPUMP.ADD_FILE(
handle => JobHandle
,filename => 'FULL_EXPDP_'||InstanceName||'_'||JobStamp||'.dpf'
,directory => 'DATAPUMP_DIR'
,filetype => 1 );
 dbms_datapump.set_parameter(handle => JobHandle, name => 'KEEP_MASTER', value => 0); 
-- Specify a single log file for the job
DBMS_DATAPUMP.ADD_FILE(
handle => JobHandle
,filename => 'FULL_EXPDP_'||InstanceName||'_'||JobStamp||'.log'
,directory => 'DATAPUMP_DIR'
,filetype => 3 );
dbms_datapump.set_parameter(handle => JobHandle, name => 'INCLUDE_METADATA', value => 1); 
dbms_datapump.set_parameter(handle => JobHandle, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC'); 
-- Start the job. An exception will be generated if something is not set up
-- properly.
DBMS_DATAPUMP.START_JOB(JobHandle);
 
-- The export job should now be running. In the following loop, the job
-- is monitored until it completes. In the meantime, progress information is
-- displayed.
percent_done := 0;
job_state := 'UNDEFINED';
while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
dbms_datapump.get_status(JobHandle,dbms_datapump.ku$_status_job_error +dbms_datapump.ku$_status_job_status +dbms_datapump.ku$_status_wip,-1,job_state,sts);
js := sts.job_status;
-- If the percentage done changed, display the new value.
if js.percent_done != percent_done
then
dbms_output.put_line('*** Job percent done = ' ||
to_char(js.percent_done));
percent_done := js.percent_done;
end if;
-- If any work-in-progress (WIP) or error messages were received for the job,
-- display them.
if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0)
then
le := sts.wip;
else
if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
then
le := sts.error;
else
le := null;
end if;
end if;
if le is not null
then
ind := le.FIRST;
while ind is not null loop
dbms_output.put_line(le(ind).LogText);
ind := le.NEXT(ind);
end loop;
end if;
end loop;
-- Indicate that the job finished and detach from it.
dbms_output.put_line('Job has completed');
dbms_output.put_line('Final job state = ' || job_state);
dbms_datapump.detach(JobHandle);
 
END pr_expdp_full;"

Par contre, une fois planifié dans le GRID, je ne trouve pas le moyen de renvoyer la sortie de l'écran (dbms_output.put_line) et le log de l'export dans le journal d'exécution du Grid directement.

Or, apparemment quand on passe par les menus d'exports proposés en natif dans le Grid, on voit bien tout le log dans l'onglet détail d’exécution.

Quelqu'un a t il une idée ?

D'avance merci.