Home » SQL & PL/SQL » SQL & PL/SQL » DataPump Network Import PL/SQL
DataPump Network Import PL/SQL [message #190184] Tue, 29 August 2006 10:27
scoutert24
Messages: 1
Registered: August 2006
Location: Cedar Rapids
Junior Member
I have created a stored procedure to run a datapump network import (take data from one instance and insert it into another without any datafiles). When I run the procedure it does not give any errors, but no data is moved.

Thanks for any Help!

OS: SUSE Enterprise 9.3 x86_64
DB: Oracle 10.1.0.3 x86_64

Code:
PROCEDURE MOVE_PROJECT (v_dblink IN VARCHAR2, v_project IN VARCHAR2) IS
   ind NUMBER;             	
   jobhandle NUMBER;       	
   percent_done NUMBER;    	
   job_state VARCHAR2(30); 	
   le ku$_LogEntry;        	
   js ku$_JobStatus;       	
   jd ku$_JobDesc;         	
   sts ku$_Status;         	

BEGIN
  dbms_output.put_line('IN ('''||v_project||''')');
  jobhandle := DBMS_DATAPUMP.OPEN('IMPORT','SCHEMA', v_dblink, '1Project_Move_'||v_project);
  dbms_output.put_line(jobhandle) ;
  DBMS_DATAPUMP.METADATA_FILTER(jobhandle,'SCHEMA_EXPR','IN ('''||v_project||''')');
  DBMS_DATAPUMP.SET_PARAMETER(jobhandle,'TABLE_EXISTS_ACTION','SKIP');
  DBMS_DATAPUMP.SET_PARAMETER(jobhandle,'ESTIMATE','STATISTICS');
  DBMS_DATAPUMP.START_JOB(jobhandle);

  percent_done := 0;
  job_state := 'UNDEFINED';

  while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
    DBMS_DATAPUMP.get_status(jobhandle,
      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 js.percent_done != percent_done
    then
      percent_done := js.percent_done;
    end if;

    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
        ind := le.NEXT(ind);
      end loop;
    end if;
  end loop;

  DBMS_OUTPUT.PUT_LINE('Job has completed');
  DBMS_OUTPUT.PUT_LINE('Final job state = ' || job_state);
  DBMS_DATAPUMP.DETACH(jobhandle);
  dbms_output.put_line('Done');

  END_DATABASE_PROGRESS ;
END MOVE_PROJECT ;
Previous Topic: function returning array
Next Topic: How to Change One word in a particular column
Goto Forum:
  


Current Time: Fri Dec 09 15:29:24 CST 2016

Total time taken to generate the page: 0.09899 seconds