asked on

How to replace id-value with new fictive id in oracle

Hi!

I have a table with, let´s say, 4 columns, person, colA, col B and colC.

The first column contains personal number (ssn) so when I export data to outside persons I dont want to send also the actual value in column. Instead I want to replace, in this case "760320" with "1" and 760914 with "2". Please note that 760320 and other personal numbers can occure 1, 2 or several times.

person      col A        col B        col C
760320      x        y        z
760320      x        y        z
760914      x        y        u

The result should be

person col A colB col C
1 x y z
1 x y z
2 x y u

Thanks in advance

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

>>when I export data to outside

Do you mean Oracle Export?

If you are running 11g and plan on using datapump, check out:
Data Pump Data Remapping (Obfuscation)

http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/DPUMP4/Default.aspx

If you mean some other type of expert, please clarify.

marcgu

ASKER

Hi!

Thanks for really fast possible solutions.

sdstuber: I will try your solution at work tomorrow.

slightwv: I wrote export, but the only import thing is really to get the result from the select-query in SQL developer . From there I can choose to copy the rows from the select or right click to export. Sorry if this caused false focus.
/Marcus

slightwv (䄆 Netminder)

Are you only wanting a single table or will you be exporting additional related tables and need to keep the obfuscated values equal across the exports?

marcgu

ASKER

Thanks for perfect solution.
/Marcus