I use\u00a0Enterprise Manager Cloud Control to mask Oracle databases so they can\u00a0be shared with our ERP vendor. I intended to do the same with our legacy Informix database but I found that we did not have the Database Gateway licenses required for using Enterprise Manager Data Masking Pack on Informix. We also had a requirement to replace customer names and addresses with a set of twenty arbitrary values. Due to referential integrity, the replacement values had to be deterministic\/repeatable – not random.<\/p>\n
The Informix DBA sent me flat file exports from the Informix tables containing the customer records. I mounted these as external tables and wrote SQL to select from them and write to Oracle tables. This was so slow that I abandoned that idea and decided to load the files into Oracle tables using SQL Loader. It’s been so long since I have used SQL Loader that I had to relearn how to build control files. I was amazed at how fast the data loaded. I had the idea of using\u00a0SQL Loader to mask the data during the load.<\/p>\n
I decided to use the oracle_hash function inside a case statement in the SQL Loader control file specification for the sensitive columns. This let me replace the names and addresses with new values in a repeatable way. I forced the names to uppercase and then produced a hash value which I associated with the fictional name. That fictional name got loaded into the Oracle table.<\/p>\n
I am very satisfied with the results and happy I had the opportunity to re-familiarize myself with SQL Loader.<\/p>\n
Here’s the content of the control file:<\/p>\n
\noptions
\n(
\ndirect=true, rows=200000, multithreading=true
\n)
\nunrecoverable
\nload data
\ninfile customers.unl badfile customers.bad discardfile customers.dis
\ninto table customers
\nfields terminated by ‘|’
\n(
\ncust_num,
\nfirst_name “case
\nwhen (ora_hash(upper(:first_name),20,0) = 0) then ‘Andy’
\nwhen (ora_hash(upper(:first_name),20,0) = 1) then ‘Brenda’
\nwhen (ora_hash(upper(:first_name),20,0) = 2) then ‘Jim’
\nwhen (ora_hash(upper(:first_name),20,0) = 3) then ‘Cathy’
\nwhen (ora_hash(upper(:first_name),20,0) = 4) then ‘Bob’
\nwhen (ora_hash(upper(:first_name),20,0) = 5) then ‘Sue’
\nwhen (ora_hash(upper(:first_name),20,0) = 6) then ‘Joe’
\nwhen (ora_hash(upper(:first_name),20,0) = 7) then ‘Carmen’
\nwhen (ora_hash(upper(:first_name),20,0) = 8) then ‘Jose’
\nwhen (ora_hash(upper(:first_name),20,0) = 9) then ‘Claire’
\nwhen (ora_hash(upper(:first_name),20,0) = 10) then ‘Ron’
\nwhen (ora_hash(upper(:first_name),20,0) = 11) then ‘Misty’
\nwhen (ora_hash(upper(:first_name),20,0) = 12) then ‘Jason’
\nwhen (ora_hash(upper(:first_name),20,0) = 13) then ‘Glenda’
\nwhen (ora_hash(upper(:first_name),20,0) = 14) then ‘Rick’
\nwhen (ora_hash(upper(:first_name),20,0) = 15) then ‘Suzy’
\nwhen (ora_hash(upper(:first_name),20,0) = 16) then ‘Peter’
\nwhen (ora_hash(upper(:first_name),20,0) = 17) then ‘Sara’
\nwhen (ora_hash(upper(:first_name),20,0) = 18) then ‘Isaac’
\nwhen (ora_hash(upper(:first_name),20,0) = 19) then ‘Helen’
\nwhen (ora_hash(upper(:first_name),20,0) = 20) then ‘Nathan’
\nelse ‘ERROR’ end”,
\nlast_name “case
\nwhen (ora_hash(upper(:last_name),20,0) = 0) then ‘Jones’
\nwhen (ora_hash(upper(:last_name),20,0) = 1) then ‘Hay’
\nwhen (ora_hash(upper(:last_name),20,0) = 2) then ‘Smith’
\nwhen (ora_hash(upper(:last_name),20,0) = 3) then ‘Gonzales’
\nwhen (ora_hash(upper(:last_name),20,0) = 4) then ‘Wu’
\nwhen (ora_hash(upper(:last_name),20,0) = 5) then ‘Cho’
\nwhen (ora_hash(upper(:last_name),20,0) = 6) then ‘Einstein’
\nwhen (ora_hash(upper(:last_name),20,0) = 7) then ‘Ward’
\nwhen (ora_hash(upper(:last_name),20,0) = 8) then ‘Magee’
\nwhen (ora_hash(upper(:last_name),20,0) = 9) then ‘Morgan’
\nwhen (ora_hash(upper(:last_name),20,0) = 10) then ‘Tenkiller’
\nwhen (ora_hash(upper(:last_name),20,0) = 11) then ‘Rice’
\nwhen (ora_hash(upper(:last_name),20,0) = 12) then ‘Namath’
\nwhen (ora_hash(upper(:last_name),20,0) = 13) then ‘DeMarco’
\nwhen (ora_hash(upper(:last_name),20,0) = 14) then ‘Obama’
\nwhen (ora_hash(upper(:last_name),20,0) = 15) then ‘Bush’
\nwhen (ora_hash(upper(:last_name),20,0) = 16) then ‘Grant’
\nwhen (ora_hash(upper(:last_name),20,0) = 17) then ‘Lee’
\nwhen (ora_hash(upper(:last_name),20,0) = 18) then ‘Blount’
\nwhen (ora_hash(upper(:last_name),20,0) = 19) then ‘Bain’
\nwhen (ora_hash(upper(:last_name),20,0) = 20) then ‘Adams’
\nelse ‘ERROR’ end”,
\naddress1 “case
\nwhen (ora_hash(upper(:address1),20,0) = 0) then ’10 Main’
\nwhen (ora_hash(upper(:address1),20,0) = 1) then ’11 1st’
\nwhen (ora_hash(upper(:address1),20,0) = 2) then ’22 2nd’
\nwhen (ora_hash(upper(:address1),20,0) = 3) then ’33 3rd’
\nwhen (ora_hash(upper(:address1),20,0) = 4) then ’44 4th’
\nwhen (ora_hash(upper(:address1),20,0) = 5) then ’55 5th’
\nwhen (ora_hash(upper(:address1),20,0) = 6) then ’66 6th’
\nwhen (ora_hash(upper(:address1),20,0) = 7) then ’77 7th’
\nwhen (ora_hash(upper(:address1),20,0) = 8) then ’88 8th’
\nwhen (ora_hash(upper(:address1),20,0) = 9) then ’99 9th’
\nwhen (ora_hash(upper(:address1),20,0) = 10) then ‘100 10th’
\nwhen (ora_hash(upper(:address1),20,0) = 11) then ‘111 11th’
\nwhen (ora_hash(upper(:address1),20,0) = 12) then ‘222 22nd’
\nwhen (ora_hash(upper(:address1),20,0) = 13) then ‘333 33rd’
\nwhen (ora_hash(upper(:address1),20,0) = 14) then ‘444 44th’
\nwhen (ora_hash(upper(:address1),20,0) = 15) then ‘555 55th’
\nwhen (ora_hash(upper(:address1),20,0) = 16) then ‘666 66th’
\nwhen (ora_hash(upper(:address1),20,0) = 17) then ‘777 77th’
\nwhen (ora_hash(upper(:address1),20,0) = 18) then ‘888 88th’
\nwhen (ora_hash(upper(:address1),20,0) = 19) then ‘999 99th’
\nwhen (ora_hash(upper(:address1),20,0) = 20) then ‘2000 20th’
\nelse ‘ERROR’ end”,
\ncity,
\nstate,
\nzip,
\ncountry_code,
\nphone “upper(‘8005551212’)”,
\nemail “upper(‘me\\@mydomain.com’)” ,
\ncreate_date date ‘mm\/dd\/yyyy’,
\n)<\/p>\n<\/blockquote>\n