Apply specific function to each row that is being exported (REMAP_DATA)

Using REMAP_DATA parameter you can apply a function to the rows that being exported and get a remapped data as a result.

 

In the following example, we create a function that accepts a string as an input and converts the string to uppercase. Then we use this function with REMAP_DATA parameter and remap the characters of specific column of the table.

 

Create a package with one function which converts input string to uppercase and return it:

 

SQL> CREATE OR REPLACE PACKAGE usr_dptest1.pkg_remap

AS

FUNCTION make_upper (p_val VARCHAR2)

RETURN VARCHAR2;

END;

/

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY usr_dptest1.pkg_remap

AS

FUNCTION make_upper (p_val VARCHAR2)

RETURN VARCHAR2

IS

BEGIN

RETURN UPPER (p_val);

END;

END;

/

Package body created.

 

Create a table with 5 rows and make values of OBJ_NAME column lowercase:

 

SQL> CREATE TABLE usr_dptest1.all_lowers

AS

SELECT LOWER (object_name) obj_name, object_id, object_typeFROM dba_objects

WHERE ROWNUM <= 5;

Table created.




SQL> SET LINESIZE 150




SQL> COL obj_name FORMAT a15




SQL> SELECT * FROM usr_dptest1.all_lowers ;

OBJ_NAME OBJECT_ID OBJECT_TYPE

--------------- ---------- -------------------

icol$ 20 TABLE

i_user1 46 INDEX

con$ 28 TABLE

undo$ 15 TABLE

c_cobj# 29 CLUSTER

 

Create a parameter file and use REMAP_DATA parameter to convert values of OBJ_NAME column to uppercase:

 

[oracle@oemgrid ~]$ more /tmp/parfile03.dat

directory=my_dir

dumpfile=exp_remap_data_01.dmp

schemas=usr_dptest1

include=table:”IN (‘ALL_LOWERS’)”

remap_data=usr_dptest1.all_lowers.obj_name:usr_dptest1.pkg_remap.make_upper

[oracle@oemgrid ~]$

Export the dump file, drop the table, import it back and query the table. All rows of column OBJ_NAME will be converted to uppercase:

 

[oracle@oemgrid ~]$ expdp system/oracle parfile=/tmp/parfile03.dat

Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_03”: system/******** parfile=/tmp/parfile03.dat

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type SCHEMA_EXPORT/TABLE/TABLE

. . exported “USR_DPTEST1”.”ALL_LOWERS” 5.937 KB 5 rows

Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_03” successfully loaded/unloaded

[oracle@oemgrid ~]$




SQL> DROP TABLE usr_dptest1.all_lowers ;

Table dropped.

SQL>




[oracle@oemgrid ~]$ impdp system/oracle directory=my_dir dumpfile=exp_remap_data_01.dmp

Master table “SYSTEM”.”SYS_IMPORT_FULL_01” successfully loaded/unloaded

Starting “SYSTEM”.”SYS_IMPORT_FULL_01”: system/******** directory=my_dir dumpfile=exp_remap_data_01.dmp

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported “USR_DPTEST1”.”ALL_LOWERS” 5.937 KB 5 rows

Job “SYSTEM”.”SYS_IMPORT_FULL_01” successfully completed at 03:37:10

[oracle@oemgrid ~]$ sqlplus / as sysdba




SQL> SET LINESIZE 150

SQL> COL obj_name FORMAT a20




SQL> SELECT * FROM usr_dptest1.all_lowers;

OBJ_NAME OBJECT_ID OBJECT_TYPE

-------------------- ---------- -------------------

ICOL$ 20 TABLE

I_USER1 46 INDEX

CON$ 28 TABLE

UNDO$ 15 TABLE

C_COBJ# 29 CLUSTER

SQL>

 

 

Leave a reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>