Generic datapump scripts

datapump_export.sql

DECLARE
   num_handle NUMBER;
   str_filename VARCHAR2(2000 Char);
   clb_tablelist CLOB;
   str_directory VARCHAR2(30 Char) := 'LOADING_DOCK';
   
BEGIN
   str_filename := 'waters_cip_OWPUB_20170823';
   
   --Note you must single quote the table items
   clb_tablelist := '''CATCHMENT_STATE''';
      
   num_handle := DBMS_DATAPUMP.open(
       operation => 'EXPORT'
      ,job_mode  => 'TABLE'
      ,job_name  => 'EXPORT_DUMPIT_' || TO_CHAR(SYSDATE,'YYYY_MMDD')
      ,version   => 'COMPATIBLE'
   );
      
   DBMS_DATAPUMP.add_file(
       handle    => num_handle
      ,filename  => str_filename || '.log'
      ,directory => str_directory
      ,filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
      ,reusefile => 1
   );
           
   DBMS_DATAPUMP.add_file(
       handle    => num_handle
      ,filename  => str_filename || '.dmp'
      ,directory => str_directory
      ,filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
      ,reusefile => 1
   );
   
   DBMS_DATAPUMP.metadata_filter(
       handle    => num_handle
      ,name      => 'NAME_LIST'
      ,value     => clb_tablelist
    );
     
   DBMS_DATAPUMP.SET_PARAMETER(
       handle    => num_handle
      ,name      => 'COMPRESSION'
      ,value     => 'ALL'
   );

   DBMS_DATAPUMP.start_job(
       handle    => num_handle
   );
      
   DBMS_DATAPUMP.detach(
       handle   => num_handle
   );

   EXCEPTION 
      WHEN OTHERS
      THEN
         BEGIN
            DBMS_DATAPUMP.detach(
               handle => num_handle
            );
            
         EXCEPTION
            WHEN OTHERS
            THEN
               NULL;
         END;

         RAISE;
   
END;
/

datapump_import.sql

DECLARE
   num_handle    NUMBER;
   str_filename  VARCHAR2(2000 Char);
   str_directory VARCHAR2(30 Char) := 'LOADING_DOCK';
   
BEGIN
   str_filename := 'waters_cip_OWPUB_20170820';
      
   num_handle := DBMS_DATAPUMP.open(
       operation => 'IMPORT'
      ,job_mode  => 'FULL'
      ,job_name  => 'IMPORT_LOADIT_' || TO_CHAR(SYSDATE,'YYYY_MMDD')
      ,version   => 'COMPATIBLE'
   );
      
   DBMS_DATAPUMP.add_file(
       handle    => num_handle
      ,filename  => str_filename || '.log'
      ,directory => str_directory
      ,filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
      ,reusefile => 1
   );
           
   DBMS_DATAPUMP.add_file(
       handle    => num_handle
      ,filename  => str_filename || '.dmp'
      ,directory => str_directory
      ,filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
      ,reusefile => 1
   );
     
   DBMS_DATAPUMP.SET_PARAMETER(
       handle    => num_handle
      ,name      => 'TABLE_EXISTS_ACTION'
      ,value     => 'REPLACE'
   );

   DBMS_DATAPUMP.start_job(
       handle    => num_handle
   );
      
   DBMS_DATAPUMP.detach(
       handle   => num_handle
   );

   EXCEPTION 
      WHEN OTHERS
      THEN
         BEGIN
            DBMS_DATAPUMP.detach(
               handle => num_handle
            );
            
         EXCEPTION
            WHEN OTHERS
            THEN
               NULL;
         END;

         RAISE;
   
END;
/