expdp loop dans plsql

Bonjour,

j'aurais une simple question.

J'exécute un export via expdp

voici le code

Code:

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
115
116
117
118
create or replace
PACKAGE BODY epss_export AS
PROCEDURE archive_call (call_id IN number) IS
 
  h1   NUMBER;
  call_id_export NUMBER := call_id ;
  export_job_name varchar2(20) := 'CALL_' ||call_id_export ;  
 
 
begin  
  begin 
      h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'TABLE', job_name => ''||export_job_name||'', version => 'COMPATIBLE'); 
  end;
  begin 
     dbms_datapump.set_parallel(handle => h1, degree => 1); 
  end;
  begin 
     dbms_datapump.add_file(handle => h1, filename => 'EXPDAT_'||call_id_export||'.LOG', directory => 'EXPDP_DIR', filetype => 3); 
  end;
  begin 
     dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0); 
  end;
  begin 
     dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''EPSS'')'); 
  end;
  begin 
     dbms_datapump.metadata_filter(handle => h1, name => 'NAME_EXPR', value => 'IN(''EPSS_ASSESSMENT_INFORMATIONS'',''EPSS_CALL'',''EPSS_CALL_COORDINATORS'',''EPSS_CALL_SETUP_TYPE'',''EPSS_CONTINUOUS_DEADLINES'',''EPSS_EPT_PACKAGES'',''EPSS_FILES'',''EPSS_FILES_SUBMITTED'',''EPSS_FORMS'',''EPSS_FORMS_A3_2'',''EPSS_FORMS_A3_2_SUBMITTED'',''EPSS_FORMS_SUBMITTED'',''EPSS_GEN_ACTIVITY_CODES'',''EPSS_HISTORY'',''EPSS_INSTRUMENT'',''EPSS_INSTRUMENT_LIST'',''EPSS_INSTRUMENT_ACTIVITY_CODE'',''EPSS_KEYWORDS'',''EPSS_KEYWORDS_FORM'',''EPSS_MOBILITY_PARTNERS'',''EPSS_PARTNER'',''EPSS_PIC_PROPOSAL'',''EPSS_PRINCIPLE_PARTNERS'',''EPSS_PROPOSAL'',''EPSS_REFEREES'',''EPSS_REGISTRATION'',''EPSS_STATE'',''EPSS_SPEC_ACTIVITY_CODES'',''EPSS_TEMPLATEFORMS'',''EPSS_USERS'',''EPSS_WORKFLOWSTATE'',''EPSS_XMLTEMPLATEFORMS'',''EPSS_WELCOME_PAGE'')'); 
  end;
  begin 
     dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS'); 
  end;
  begin 
     dbms_datapump.add_file(handle => h1, filename => 'EXPDAT_'||call_id_export||'_%U.DMP', directory => 'EXPDP_DIR', filetype => 1); 
  end;
  begin 
     dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 0); 
  end;
  begin 
     dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC'); 
  end;
  begin 
     dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'where call_id = '||call_id_export||'', table_name => 'EPSS_ASSESSMENT_INFORMATIONS', schema_name => 'EPSS'); 
  end;
  begin 
     dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'where call_id = '||call_id_export||'', table_name => 'EPSS_CALL', schema_name => 'EPSS'); 
  end;
  begin 
     dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'where call_id = '||call_id_export||'', table_name => 'EPSS_CALL_COORDINATORS', schema_name => 'EPSS'); 
  end;
  begin 
     dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'where call_id = '||call_id_export||'', table_name => 'EPSS_EPT_PACKAGES', schema_name => 'EPSS'); 
  end;
  begin 
     dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'where call_id = '||call_id_export||'', table_name => 'EPSS_FILES', schema_name => 'EPSS'); 
  end;
  begin 
     dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'where call_id = '||call_id_export||'', table_name => 'EPSS_FILES_SUBMITTED', schema_name => 'EPSS'); 
  end;
  begin 
     dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'where call_id = '||call_id_export||'', table_name => 'EPSS_FORMS', schema_name => 'EPSS'); 
  end;  
  begin 
     dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'where call_id = '||call_id_export||'', table_name => 'EPSS_FORMS_A3_2', schema_name => 'EPSS'); 
  end;
  begin 
     dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'where call_id = '||call_id_export||'', table_name => 'EPSS_FORMS_A3_2_SUBMITTED', schema_name => 'EPSS'); 
  end;
  begin 
     dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'where call_id = '||call_id_export||'', table_name => 'EPSS_FORMS_SUBMITTED', schema_name => 'EPSS'); 
  end;
  begin 
     dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'where call_id = '||call_id_export||'', table_name => 'EPSS_HISTORY', schema_name => 'EPSS'); 
  end;
  begin 
     dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'where call_id = '||call_id_export||'', table_name => 'EPSS_INSTRUMENT', schema_name => 'EPSS'); 
  end;
  begin 
     dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'where call_id = '||call_id_export||'', table_name => 'EPSS_INSTRUMENT_ACTIVITY_CODE', schema_name => 'EPSS'); 
  end;  
  begin 
     dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'where call_id = '||call_id_export||'', table_name => 'EPSS_MOBILITY_PARTNERS', schema_name => 'EPSS'); 
  end;
  begin 
     dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'where call_id = '||call_id_export||'', table_name => 'EPSS_PARTNER', schema_name => 'EPSS'); 
  end;
  begin 
     dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'where call_id = '||call_id_export||'', table_name => 'EPSS_PRINCIPLE_PARTNERS', schema_name => 'EPSS'); 
  end;
  begin 
     dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'where call_id = '||call_id_export||'', table_name => 'EPSS_PIC_PROPOSAL', schema_name => 'EPSS'); 
  end;
  begin 
     dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'where call_id = '||call_id_export||'', table_name => 'EPSS_PROPOSAL', schema_name => 'EPSS'); 
  end;
  begin 
     dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'where call_id = '||call_id_export||'', table_name => 'EPSS_REFEREES', schema_name => 'EPSS'); 
  end;
  begin 
     dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'where call_id = '||call_id_export||'', table_name => 'EPSS_REGISTRATION', schema_name => 'EPSS'); 
  end;  
  begin 
     dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'where call_id in '||call_id_export||'', table_name => 'EPSS_TEMPLATEFORMS', schema_name => 'EPSS'); 
  end;
  begin 
     dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'where call_id = '||call_id_export||'', table_name => 'EPSS_WORKFLOWSTATE', schema_name => 'EPSS'); 
  end;
  begin 
     dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'where call_id = '||call_id_export||'', table_name => 'EPSS_XMLTEMPLATEFORMS', schema_name => 'EPSS'); 
  end;
  begin 
     dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0); 
  end;
  begin 
     dbms_datapump.detach(handle => h1); 
  end;
end;
 
end epss_export;

Le truc assez ennuyant, c'est qu'actuellement j'exécute ce script manuellement via sql developer.

Et par exemple aujourd'hui, je devrai le lancer 48 fois car j'ai 48 archivage à faire.

Ma question est de savoir si je peux inclure une boucle dans mon script qui me permettrait de le lancer un seule fois.

Je pense que la réponse est clairement oui.

Maintenant mon autre question. Est ce que le "detach" termine l'export physiquement dans mon fichier dump ?? Ou alors termine-t-il ma connexion avec la base alors que l'export continue en tâche de fond ??

Pour moi c'est important car j'aimerais que ce traitement se fasse en série et non en parallèle.

Connexion --> boucle --> export physique terminé --> boucle --> export physique terminé .......

Voilà.

Merci d'avance pour vos réponses.