Thanks for the question, Soumik.
Asked: September 06, 2018 - 10:09 am UTC
Last updated: September 12, 2018 - 4:38 am UTC
Version: 11g
Viewed 1000+ times
You Asked
Hi Tom,
Trying to apply remap_data to multiple tables it always fails.
The column name is same across both tables where i am applying the function,
it exports and imports without any errors from the source schema to the target schema, but it never applies the function which is intended for the same.
Export
=============
Starting "XXXX"."SYS_EXPORT_TABLE_01": xxxx/******** directory=expdp_dir dumpfile=test0409.dmp logfile=test0409.log
remap_data=xxxx.tablename1.column_name:xxxx.pkg_test2.mask,xxxx.tablename2.column_name:xxxx.pkg_test2.mask
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "XXXX"."TABLENAME1" 22.24 KB 99 rows
. . exported "XXXX"."TABLENAME2" 22.68 KB 99 rows
Master table "XXXX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for XXXX.SYS_EXPORT_TABLE_01 is:
/backup/oracle/export/XXXX/test0409.dmp
Job "XXXX"."SYS_EXPORT_TABLE_01" successfully completed at 11:30:55
Import
=======
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "XXXX"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XXXX"."SYS_IMPORT_FULL_01": xxxx/******** directory=expdp_dir dumpfile=test0409.dmp logfile=testimp0409.log remap_schema=xxxx:xxxx_d
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XXXX_D"."TABLENAME1" 22.24 KB 99 rows
. . imported "XXXX_D"."TABLENAME2" 22.68 KB 99 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "XXXX"."SYS_IMPORT_FULL_01" successfully completed at 11:31:51
The Function
=============
create or replace PACKAGE BODY pkg_test2 AS
FUNCTION mask (
nrr_number_low IN VARCHAR2
) RETURN VARCHAR2 AS
m_len NUMBER := 0;
m_no_masked VARCHAR2(10) := NULL;
BEGIN
dbms_output.put_line('ORIGINAL MOBILE NUMBER: ' || nrr_number_low);
m_no_masked := substr(nrr_number_low,1,4)
|| regexp_replace(substr(nrr_number_low,5,6),'1|2|3|4|5|6|7|8','*');
dbms_output.put_line('MASKED MOBILE NUMBER: ' || m_no_masked);
RETURN m_no_masked;
END mask;
END;
and Connor said...
Here's my complete test case
SQL> drop user demo cascade;
User dropped.
SQL> create user demo identified by demo;
User created.
SQL> grant connect, resource to demo;
Grant succeeded.
SQL> alter user demo quota 100m on users;
User altered.
SQL> create table demo.t1 ( m varchar2(20));
Table created.
SQL>
SQL> insert into demo.t1 values ('123123123');
1 row created.
SQL> insert into demo.t1 values ('763134123');
1 row created.
SQL> insert into demo.t1 values ('233145123');
1 row created.
SQL> insert into demo.t1 values ('433123123');
1 row created.
SQL>
SQL> create table demo.t2 as select * from demo.t1;
Table created.
SQL> commit;
Commit complete.
SQL>
SQL> grant read, write on directory temp to demo;
Grant succeeded.
SQL>
SQL> drop user demo1 cascade;
User dropped.
SQL> create user demo1 identified by demo1;
User created.
SQL> grant connect, resource to demo1;
Grant succeeded.
SQL> alter user demo1 quota 100m on users;
User altered.
SQL> grant read, write on directory temp to demo1;
Grant succeeded.
C:\temp>expdp demo/demo directory=TEMP dumpfile=demo.dmp
Export: Release 12.2.0.1.0 - Production on Wed Sep 12 12:33:36 2018
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "DEMO"."SYS_EXPORT_SCHEMA_01": demo/******** directory=TEMP dumpfile=demo.dmp
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "DEMO"."T2" 5.101 KB 4 rows
. . exported "DEMO"."T1" 5.101 KB 4 rows
Master table "DEMO"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DEMO.SYS_EXPORT_SCHEMA_01 is:
C:\TEMP\DEMO.DMP
Job "DEMO"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Sep 12 12:33:53 2018 elapsed 0 00:00:17
SQL> create or replace
2 PACKAGE demo1.pkg_test2 AS
3
4 FUNCTION mask (
5 nrr_number_low IN VARCHAR2
6 ) RETURN VARCHAR2 ;
7
8 END;
9 /
Package created.
SQL>
SQL> create or replace
2 PACKAGE BODY demo1.pkg_test2 AS
3
4 FUNCTION mask (
5 nrr_number_low IN VARCHAR2
6 ) RETURN VARCHAR2 AS
7 m_len NUMBER := 0;
8 m_no_masked VARCHAR2(10) := NULL;
9 BEGIN
10 --dbms_output.put_line('ORIGINAL MOBILE NUMBER: ' || nrr_number_low);
11 m_no_masked := substr(nrr_number_low,1,4)
12 || regexp_replace(substr(nrr_number_low,5,6),'1|2|3|4|5|6|7|8','*');
13
14 --dbms_output.put_line('MASKED MOBILE NUMBER: ' || m_no_masked);
15 RETURN m_no_masked;
16 END mask;
17
18 END;
19 /
Package body created.
C:\temp>impdp demo1/demo1 directory=TEMP remap_schema=demo:demo1 dumpfile=demo.dmp remap_data=demo.t1.m:demo1.pkg_test2.mask,demo.t2.m:demo1.pkg_test2.mask
Import: Release 12.2.0.1.0 - Production on Wed Sep 12 12:34:29 2018
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "DEMO1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DEMO1"."SYS_IMPORT_FULL_01": demo1/******** directory=TEMP remap_schema=demo:demo1 dumpfile=demo.dmp remap_data=demo.t1.m:demo1.pkg_test2.mask,demo.t2.m:demo
1.pkg_test2.mask
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "DEMO1"."T2" 5.101 KB 4 rows
. . imported "DEMO1"."T1" 5.101 KB 4 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "DEMO1"."SYS_IMPORT_FULL_01" successfully completed at Wed Sep 12 12:34:36 2018 elapsed 0 00:00:07
SQL> select * from demo1.t1;
M
----------------------------------------------
1231*****
7631*****
2331*****
4331*****
4 rows selected.
SQL> select * from demo1.t2;
M
----------------------------------------------
1231*****
7631*****
2331*****
4331*****
4 rows selected.
Looks fine to me