I am trying to create a PL/SQL script that extracts a root "object" together with all children and other relevant information from an oracle production database. The purpose is to create a set of test-data to recreate issues that are encountered in production. Due to data protection laws the data needs to be anonymized when extracted - object names, certain types of id's, and monetary amounts need to be replaced.
I was trying to create one or more temporary translation tables, which would contain both the original values and anonymized versions. Then I would join the real data with the translation tables and output the anonymized values wherever required.
DECLARE
rootId integer := 123456;
TYPE anonTableRow IS RECORD
(
id NUMBER,
fieldC NUMBER,
anonymizedFieldC NUMBER
);
TYPE anonTable IS TABLE OF anonTableRow;
anonObject anonTable;
BEGIN
FOR cursor_row IN
(
select
id,
fieldC,
1234 -- Here I would create anonymized values based on rowNum or something similar
from
prodTable
where id = rootId
)
LOOP
i := i + 1;
anonObject(i) := cursor_row;
END LOOP;
FOR cursor_row IN
(
select
prod_table.id,
prod_table.fieldB,
temp_table.anonymizedFieldC fieldC,
prod_table.fieldD
from
prod_table
inner join table(temp_table) on prod_table.id = temp_table.id
where prod_table.id = 123456789
)
LOOP
dbms_output.put_line('INSERT INTO prod_table VALUES (' || cursor_row.id || ', ' || cursor_row.fieldB || ', ' || cursor_row.fieldC || ', , ' || cursor_row.fieldD);
END LOOP;
END;
/
However I ran into several problems with this approach - it seems to be near impossible to join oracle PL/SQL tables with real database tables. My access to the production database is severely restricted, so I cannot create global temporary tables, declare types outside PL/SQL or anything of that sort.
My attempt to declare my own PL/SQL types failed with the problems mentioned in this question - the solution does not work for me because of the limited permissions.
Is there a pure PL/SQL way that does not require fancy permissions to achieve something like the above?
Please Note: The above code example is simplified a lot and would not really require a separate translation table - in reality I need access to the original and translated values in several different queries, so I would prefer not having to "recalculate" translations everywhere.
If your data is properly normalized, then I guess this should only be necessary for internal IDs (not sure why you need to translate them though).
The following code should work for you, keeping the mappings in Associative Arrays:
DECLARE
TYPE t_number_mapping IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
mapping_field_c t_number_mapping;
BEGIN
-- Prepare mapping
FOR cur IN (
SELECT 101 AS field_c FROM dual UNION ALL SELECT 102 FROM dual -- test-data
) LOOP
mapping_field_c(cur.field_c) := mapping_field_c.COUNT; -- first entry mapped to 1
END LOOP;
-- Use mapping
FOR cur IN (
SELECT 101 AS field_c FROM dual UNION ALL SELECT 102 FROM dual -- test-data
) LOOP
-- You can use the mapping when generating the `INSERT` statement
dbms_output.put_line( cur.field_c || ' mapped to ' || mapping_field_c(cur.field_c) );
END LOOP;
END;
Output:
101 mapped to 1
102 mapped to 2