Search code examples
oracleoracle11gdatapumpexpdp

Datapump REMAP_DATA using another column

I need to mask first and last name. Our requirement is to use the first name only. How can I access the FIRST_NM field when masking the LAST_NM?

LAST_NM=substr(FIRST_NM,1,4)||'LAST'

select T.EE_FIRST_NM, T.EE_LAST_NM from MY_TABLE

Original: Lakshmanan Ramaswamy

Expected Result: LaksFIRST LaksLAST

Solution

  • Looks like REMAP_DATA is available from Oracle 11g on. The issue is the REMAP_DATA parameter takes one column as input and a PL/SQL package/function to process that data; so you wouldn't really be able to figure out what first name goes with what last name. There is a good DataPump REMAP_DATA PDF on a sample package & function would work; but it won't solve your problem.

    What you could do, depending on your Oracle version, is define a virtual column named LAST_NAME_MASKED on your MY_TABLE that contains a function to do your substr, and then exclude the LAST_NM column from the DataPump export.