Import multiple data dump file using oracle API

186 Views Asked by At

I have 2 data dump files for import -

--> dev_expdp_part01.dmp --> dev_expdp_part02.dmp

I am using below stored procdure to import both the files one after another. But my stored procedure is only processing the first file - dev_expdp_part01.dmp and ignore the second dump file - dev_expdp_part02.dmp.

Please suggest what I am doing wrong in the below script,

create or replace PROCEDURE   SP_DP_REFRESH as
  ind NUMBER;              -- Loop index
  spos NUMBER;             -- String starting position
  slen NUMBER;             -- String length for output
  h1 NUMBER;               -- Data Pump job handle
  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
   BEGIN
      dbms_output.enable(1000000);
      h1 := DBMS_DATAPUMP.open (operation => 'IMPORT', job_mode => 'TABLE',
                             job_name => 'IMPORT_JOB',
                             version => '19.0');
   END;

   BEGIN
      DBMS_DATAPUMP.set_parallel (handle => h1, degree => 4);
   END;

   BEGIN
      DBMS_DATAPUMP.add_file (handle => h1,
                              filename => 'dev_refresh.log',
                              directory => 'DATA_PUMP_DIR',
                              filetype => 3
      );
   END;


   BEGIN
      DBMS_DATAPUMP.add_file (handle => h1,
                              filename => 'dev_expdp_part%U.dmp',
                              directory => 'DATA_PUMP_DIR',
                              filetype => 1
      );
   END;

   BEGIN
      DBMS_DATAPUMP.METADATA_REMAP(h1,'REMAP_SCHEMA','DBMAP2','DBMAP');
   END;

   BEGIN
      DBMS_DATAPUMP.set_parameter (handle => h1,
                                   name => 'INCLUDE_METADATA',
                                   VALUE => 0
      );
   END;

   BEGIN
      DBMS_DATAPUMP.set_parameter (handle => h1,
                                   name => 'TABLE_EXISTS_ACTION',
                                   VALUE => 'TRUNCATE'
      );
   END;



   BEGIN
      DBMS_DATAPUMP.start_job (handle => h1, skip_current => 0, abort_step => 0);
   END;

  percent_done := 0;
  job_state := 'UNDEFINED';
  while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
    dbms_datapump.get_status(h1,
           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);

   BEGIN
      DBMS_DATAPUMP.detach (handle => h1);
   END;
END;
0

There are 0 best solutions below