DBMS_DATAPUMP with remap_data

A simple example of using remap_data from with plsql rather than command line

PUMPY@EITP>create table tab1(col1 number);
Table created.
PUMPY@EITP>create table tab2(col1 number);
PUMPY@EITP>insert into tab1 values(1);
1 row created.
PUMPY@EITP>insert into tab2 values(1);
1 row created.
PUMPY@EITP>c/1/2
  1* insert into tab2 values(2)

create or replace package TESTPACKAGE

 is

  function settestid(p_TestId number) return number;

end TESTPACKAGE;

/

create or replace package body TESTPACKAGE

 as

function SetTestID(p_TestId number) return number is

v_TestId number := -1;

begin

return v_TestId;

end;

end TESTPACKAGE;

/

create or replace procedure exportData is

h1 number; -- Data Pump job handle

v_job_state varchar2(4000);

begin

h1 := dbms_datapump.open(operation => 'EXPORT', job_mode => 'TABLE', job_name => 'HARRY');

dbms_datapump.add_file(h1, 'example1.dmp', 'DATA_PUMP_DIR');

dbms_datapump.metadata_filter(handle => h1, name => 'NAME_EXPR', value => 'IN (select TABLE_NAME from USER_TABLES)', object_type => 'TABLE');

dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'WHERE col1 = 1', table_name => null, schema_name => null);

dbms_datapump.data_remap(handle => h1, name => 'COLUMN_FUNCTION', table_name => 'TAB1', column => 'COL1', function => 'PUMPY.TESTPACKAGE.SETTESTID', schema => 'PUMPY');

dbms_datapump.start_job(h1);

DBMS_DATAPUMP.WAIT_FOR_JOB(h1, v_job_state);

DBMS_OUTPUT.PUT_LINE(v_job_state);

end;

/

PUMPY@EITP>exec exportData;
PL/SQL procedure successfully completed.
PUMPY@EITP>drop table tab1;
Table dropped.
PUMPY@EITP>drop table tab2;
Table dropped.
PUMPY@EITP>exit
[oracle@sl02190]:EITP:/oracle/export/EITP# impdp pumpy/pumpy dumpfile=example1.dmp
Import: Release 11.2.0.3.0 - Production on Wed Apr 10 17:08:49 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Master table "PUMPY"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "PUMPY"."SYS_IMPORT_FULL_01":  pumpy/******** dumpfile=example1.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "PUMPY"."TAB1"                              5.007 KB       1 rows
. . imported "PUMPY"."TAB2"                              5.007 KB       1 rows
Job "PUMPY"."SYS_IMPORT_FULL_01" successfully completed at 17:08:51
[oracle@sl02190]:EITP:/oracle/export/EITP# sqlplus pumpy/pumpy
SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 10 17:08:59 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
PUMPY@EITP>select * from tab1;
      COL1
----------
        -1
PUMPY@EITP>select * from tab2;
      COL1
----------
         1
PUMPY@EITP>