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; |
Partager