Export in Pl/Sql via DBMS_DATAPUMP

CREATE OR REPLACE PACKAGE maintenance
AS
   PROCEDURE export_myschema;
   PROCEDURE stop_job (job_name VARCHAR2, schema_name VARCHAR2);
   PROCEDURE stop_all_jobs;
   PROCEDURE import_schema (file_name VARCHAR2);
END maintenance;
/ 

CREATE OR REPLACE PACKAGE BODY maintenance
AS
   PROCEDURE export_myschema
   AS
      handle        NUMBER;
      file_name     VARCHAR2 (200);
      log_name      VARCHAR2 (200);
      JOBNAME       VARCHAR2 (200);
      file_prefix   VARCHAR2 (30);
      dir_name      VARCHAR2 (4000);
      l_fexists BOOLEAN;
      l_file_length PLS_INTEGER;
      l_block_size BINARY_INTEGER;
   BEGIN
      dir_name := 'EXPORT_DIR';
      file_prefix := 'myschema_prod_data_';
      file_name :=
         file_prefix || MOD (TO_NUMBER (TO_CHAR (SYSDATE, 'D')), 2) -- toggle 0 or 1                                                                   || '.dmp';
      log_name :=
            file_prefix
         || MOD (TO_NUMBER (TO_CHAR (SYSDATE, 'D')), 2)
         || '_'
         || TO_CHAR (SYSDATE, 'YYMMDDHH24MISS')
         || '.log';
      JOBNAME := 'EXPORT_JOB_' || TO_CHAR (SYSDATE, 'YYMMDDHH24MISS');
      UTL_FILE.fgetattr (dir_name,
                     file_name,
                     l_fexists,
                     l_file_lenght,
                     l_block_size);
      IF l_fexists
      THEN
       DBMS_OUTPUT.put_line ( 'file to backup:'|| file_name || ' already exists - deleting it...');
       UTL_FILE.fremove (dir_name, file_name);
      ELSE
       DBMS_OUTPUT.put_line ( 'file to backup:'|| file_name || ' does not exists - continuing...');
      END IF;
      handle :=
         DBMS_DATAPUMP.open (operation   => 'EXPORT',
                             job_mode    => 'SCHEMA',
                             job_name    => JOBNAME,
                             version     => 'LATEST');
      DBMS_DATAPUMP.ADD_FILE (
         handle      => handle,
         filename    => log_name,
         directory   => dir_name,
         filetype    => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
      );
      DBMS_DATAPUMP.ADD_FILE (
         handle      => handle,
         filename    => file_name,
         directory   => dir_name,
         filetype    => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
      );
      DBMS_DATAPUMP.METADATA_FILTER (handle   => handle,
                                     name     => 'SCHEMA_EXPR',
                                     VALUE    => 'IN (''MYSCHEMA'')');
      DBMS_DATAPUMP.METADATA_FILTER (
         handle        => handle,
         name          => 'NAME_EXPR',
         VALUE         => 'NOT IN (''EMP'',''DEPT'')',
         object_type   => 'TABLE'
      );
      DBMS_DATAPUMP.START_JOB (handle);
      DBMS_DATAPUMP.DETACH (handle);
   END export_myschema; 

   PROCEDURE stop_job (job_name VARCHAR2, schema_name VARCHAR2)
   AS
      handle     NUMBER;
      day_name   VARCHAR2 (3);
   BEGIN
      handle := DBMS_DATAPUMP.ATTACH (job_name, schema_name);
      DBMS_DATAPUMP.STop_JOB (handle, 1, 0);
   END stop_job;
   PROCEDURE stop_all_jobs
   IS
      handle   NUMBER;
   BEGIN
      FOR x IN (SELECT   * FROM DBA_datapump_jobs)
      LOOP
         BEGIN
            BEGIN
               EXECUTE IMMEDIATE   'drop table '
                                || x.owner_name
                                || '.'
                                || x.job_name;
               DBMS_OUTPUT.put_line (
                  'Table Job: ' || x.job_name || ' - Dropped'
               );
            EXCEPTION
               WHEN OTHERS
               THEN
                  DBMS_OUTPUT.put_line (
                     'Table Job: ' || x.job_name || ' - does not exists'
                  );
            END;
            handle := DBMS_DATAPUMP.ATTACH (x.job_name, x.owner_name);
            DBMS_DATAPUMP.STop_JOB (handle, 1, 0);
            DBMS_OUTPUT.put_line ('Job: ' || x.job_name || ' - DONE');
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line (
                  'Job: ' || x.job_name || ' couldn''t removed'
               );
         END;
      END LOOP;
   END stop_all_jobs; 

   PROCEDURE import_schema (file_name VARCHAR2)
   AS
      handle      NUMBER;
      log_name    VARCHAR2 (200);
      JOBNAME     VARCHAR2 (200);
      dir_name    VARCHAR2 (4000);
   BEGIN
      dir_name := 'IMPORT_DIR';
 
      log_name := 'import_' || TO_CHAR (SYSDATE, 'YYMMDDHH24MISS') || '.log';
      JOBNAME := 'IMPORT_JOB_' || TO_CHAR (SYSDATE, 'YYMMDDHH24MISS');
      handle :=
         DBMS_DATAPUMP.open (operation   => 'IMPORT',
                             job_mode    => 'SCHEMA',
                             job_name    => JOBNAME,
                             version     => 'LATEST');
      DBMS_DATAPUMP.ADD_FILE (
         handle      => handle,
         filename    => log_name,
         directory   => dir_name,
         filetype    => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
      );
      DBMS_DATAPUMP.ADD_FILE (
         handle      => handle,
         filename    => file_name,
         directory   => dir_name,
         filetype    => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
      );
      DBMS_DATAPUMP.METADATA_FILTER (handle   => handle,
                                     name     => 'SCHEMA_EXPR',
                                     VALUE    => 'IN (''MYSCHEMA'')');
      DBMS_DATAPUMP.SET_PARAMETER (handle   => handle,
                                   name     => 'TABLE_EXISTS_ACTION',
                                   VALUE    => 'TRUNCATE');
      DBMS_DATAPUMP.SET_PARALLEL (handle => handle, degree => 4);
      DBMS_DATAPUMP.START_JOB (handle);
      DBMS_DATAPUMP.DETACH (handle);
   END import_schema; 

END maintenance;
/