Oracle:使用临时表、只读权限进行纯PL/SQL数据提取和匿名化

Question

我正在尝试创建一个PL/SQL脚本，该脚本从oracle生产数据库中提取根"对象"以及所有子对象和其他相关信息。其目的是创建一组测试数据，以重新创建生产中遇到的问题。根据数据保护法，提取数据时需要匿名——对象名称、某些类型的id和货币金额需要替换。

我试图创建一个或多个临时翻译表，其中包含原始值和匿名版本。然后，我会将真实数据与转换表连接起来，并根据需要输出匿名值。

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;
/

然而，我在这种方法中遇到了一些问题——将oraclePL/SQL表与真实的数据库表连接起来似乎几乎是不可能的。我对生产数据库的访问受到严格限制，所以我不能创建全局临时表，不能在PL/SQL或任何类似的东西之外声明类型

我尝试声明自己的PL/SQL类型失败了，出现了这个问题中提到的问题——由于权限有限，该解决方案对我不起作用。

有没有一种纯粹的PL/SQL方式不需要花哨的权限就可以实现上面的功能？

请注意：上面的代码示例被简化了很多，实际上并不需要单独的翻译表——事实上，我需要在几个不同的查询中访问原始值和翻译值，所以我不希望在任何地方都"重新计算"翻译。

Answer 1

如果数据被正确规范化，那么我想这应该只对内部ID是必要的（但不确定为什么需要翻译它们）。

以下代码应该适用于您，将映射保留在关联数组中：

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中定义的集合类型。

使用您的模式中的这个脚本，您可以生成一个SQL Plus脚本来描述所有SYS拥有的类型：

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

运行生成的脚本将向您显示所有可以访问的脚本的结构。这个看起来可能很合适，例如：

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;