(8.0.21)
drop table tab1;
create table tab1(
col1 varchar(20)
);
insert into tab1 values('ABCDEFG');
insert into tab1 values('abcdefg');
select * from tab1;
select
concat('XXXX',substring(col1, 5))
into outfile '/tmp/tab1.dmp'
fields terminated by ',' optionally enclosed by '"'
from tab1;
truncate table tab1;
load data infile '/tmp/tab1.dmp'
into table tab1
fields terminated by ',' optionally enclosed by '"'
;
select * from tab1;
(19c)
drop table tab1 purge;
create table tab1(
col1 varchar2(20)
);
insert into tab1 values('ABCDEFG');
insert into tab1 values('abcdefg');
commit;
select * from tab1;
create or replace package pkg_remap as
function fun_mask (p1 in varchar2) return varchar2;
end pkg_remap;
/
create or replace package body pkg_remap as
function fun_mask (p1 in varchar2) return varchar2 is
begin
return 'XXXX' || substr(p1,5);
end;
end pkg_remap;
/
expdp test/test@pdb1 directory=ORA_DIR dumpfile=tab1.dmp logfile=tab1.exp.log tables=tab1 remap_data=test.tab1.col1:test.pkg_remap.fun_mask
truncate table tab1;
impdp test/test@pdb1 directory=ORA_DIR dumpfile=tab1.dmp logfile=tab1.imp.log tables=tab1 content=data_only
select * from tab1;
(13)
drop table tab1;
create table tab1(
col1 varchar(20)
);
insert into tab1 values('ABCDEFG');
insert into tab1 values('abcdefg');
select * from tab1;
copy ( select 'XXXX' || substr(col1,5) from tab1 ) to '/tmp/tab1.dmp' ( format csv );
truncate table tab1;
copy tab1 from '/tmp/tab1.dmp' ( format csv );
select * from tab1;
(2019)
drop table tab1;
create table tab1(
col1 varchar(20)
);
insert into tab1 values('ABCDEFG');
insert into tab1 values('abcdefg');
select * from tab1;
bcp "select 'XXXX' + substring(col1,5,20) from test.dbo.tab1" queryout "C:\tmp\tab1.dmp" -T -c -t,
truncate table tab1;
bcp test.dbo.tab1 in "C:\tmp\tab1.dmp" -T -c -t,
select * from tab1;