Oracle: Pure PL / SQL data extraction and anonymization using temporary tables, read-only permissions

Question

I am trying to create a PL / SQL script that retrieves the root "object" along with all its children and other relevant information from an oracle production database. The goal is to create a test data set to recreate production problems. Due to data protection laws, data must be anonymized when retrieving - object names, certain types of identifiers and monetary amounts must be replaced.

I tried to create one or more temporary translation tables that will contain both the original values ​​and anonymous versions. Then I would join the real data with translation tables and give out anonymous values ​​wherever needed.

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 almost impossible to combine oracle PL / SQL tables with real database tables. My access to the production database is very limited, so I cannot create global temporary tables, declare types outside of PL / SQL, or anything like that.

My attempt to declare my own PL / SQL types ended with the problems mentioned in this question - the solution does not work for me due to limited permissions.

PL/SQL, - ?

: - , "" .

Answer 1

, , ( , ).

, :

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;

:

101 mapped to 1
102 mapped to 2

Answer 2

, "" - , SYS, .

script , SQL Plus script , SYS:

select 'desc ' || type_name from all_types
where typecode = 'COLLECTION'
and owner = 'SYS';

script , . , :

SQL> desc KU$_PARAMVALUES1010
 KU$_PARAMVALUES1010 TABLE OF SYS.KU$_PARAMVALUE1010
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PARAM_NAME                                         VARCHAR2(30)
 PARAM_OP                                           VARCHAR2(30)
 PARAM_TYPE                                         VARCHAR2(30)
 PARAM_LENGTH                                       NUMBER
 PARAM_VALUE_N                                      NUMBER
 PARAM_VALUE_T                                      VARCHAR2(4000)

, , - , , .

Answer 3

. xquery flwor dbms_xmlstore. xquery .

create table mask_user_objects as select * from user_objects where rownum <0;

declare 
   v_s_table varchar2(30) := 'USER_OBJECTS';   --uppercase!!!
   v_d_table varchar2(30) := 'MASK_USER_OBJECTS';  --uppercase!!!
   v_mask_columns xmltype := xmltype('<COLS><OBJECT_NAME>XXXX</OBJECT_NAME>
                                            <DATA_OBJECT_ID>-1</DATA_OBJECT_ID>
                                            <OBJECT_TYPE/>                                            
                                      </COLS>');  --uppercase!!!   
   insCtx DBMS_XMLSTORE.ctxType;
   r NUMBER;
   v_source_table xmltype;
   v_cursor sys_refcursor; 
begin 
   open v_cursor  for 'select * from '||v_s_table||' where rownum <100 ';  
   v_source_table := xmltype(v_cursor);
   close v_cursor; 
   -- Load source table into xmltype. 
   insCtx := DBMS_XMLSTORE.newContext(v_d_table); -- Get saved context
  for rec in ( 
   select tt.column_value from  xmltable('
                  let $col := $anomyze/COLS
                  for $i in  $doc/ROWSET/ROW           
                   let $row := $i  
                   return <ROWSET>             
                            <ROW>                                         
                                {                                                                
                                 for $x in $row/* 
                                  return if(
                                       exists($col/*[name() = $x/name()] )                                                                                             
                                  ) then element{$x/name()}{ $col/*[name() = $x/name()]/text() }  
                                    else element{$x/name()}{$x/text()}                   
                                }                   
                            </ROW>             
                         </ROWSET>
                  '
                  passing v_source_table as "doc"
                       ,  v_mask_columns as "anomyze"
                  ) tt) loop
                  null;
             r := DBMS_XMLSTORE.insertXML(insCtx,  rec.column_value);
end loop;
 DBMS_XMLSTORE.closeContext(insCtx);      

end;