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.
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.