Altering row data while using Oracle expdp utility

Question

I'm a developer -not a DBA or DB expert - but as part of my job I should be able to export data to get it imported into another DB. While exporting I need to be able to alter row data that is going into dump files. Is that possible? Let's say I have a column ID with value 1,2,3,4... and while exporting I should be able to do ID * -1 for the result data -1,-2,-3,-4.... This would help me in migrating data when there's data already present in the target DB.

Answer 1

You can use the expdp REMAP_DATA option to do this, assuming you can create a package and function to do the conversion:

With some simple data:

create table t42 (id number);

insert into t42 select level from dual connect by level <= 5;

select * from t42;

        ID
----------
         1
         2
         3
         4
         5

... and a package function:

create package p42 as
    function negate(p_number in number) return number;
end p42;
/

create package body p42 as
    function negate(p_number in number) return number is
    begin
        return -1 * p_number;
    end;
end p42;
/

You can export with remap_data, and then import, in this test back into the same DB/schema:

expdp directory=data_pump_dir dumpfile=d42.dmp nologfile=yes tables=t42 \
    remap_data=t42.id:p42.negate
impdp directory=data_pump_dir dumpfile=d42.dmp nologfile=yes tables=t42 \
    content=data_only

The table now has:

select * from t42;

        ID
----------
         1
         2
         3
         4
         5
        -1
        -2
        -3
        -4
        -5

You can do the same modification on import as well, using the impdp REMAP_DATA parameter instead. The principle is the same, just depends at what point you want to modify the data.