oracle - 如何以编程方式将查询的源表重新映射到另一个表

Question

我正在研究 Oracle 11g。我得到一个用户提供的查询，例如

SELECT mycolumn
  FROM myschema.mytable

查询可以是任意复杂的子查询，并且是事先不知道的。现在我想更改查询并将源表 mytable 的所有用法重定向到另一个表 mytable_mod，无论它们在查询或子查询中的何处出现。

当然，您可以在字符串中搜索 mytable 的出现，但这是一种非常不可靠的方法，因为查询的其他部分可能具有相同的名称(例如，一列，或一些评论)

我尝试使用该语句创建 View 并使用 DBMS_METADATA 对其进行分析:

CREATE OR REPLACE VIEW MYSCHEMA.TESTVIEW as 
SELECT mycolumn FROM myschema.mytable

使用 DBMS_METADATA，您可以像这样替换模式(经过一些准备):

dbms_metadata.set_remap_param(handler, 'REMAP_SCHEMA', 
                             'MYSCHEMA', 'MY_OTHER_SCHEMA');

使用 REMAP_NAME 而不是 REMAP_SCHEMA 您可以替换对象名称。但是这两种方法都只引用 View 名称，而不引用 View 查询中使用的对象。所以结果会是

CREATE OR REPLACE VIEW MY_OTHER_SCHEMA.TESTVIEW as 
SELECT mycolumn FROM myschema.mytable

另一个想法是将创建 View 语句输出为 XML 对象。我获得了所有列定义和 View 名称，但查询本身只是 xml 中的一个文本标记。我的想法是

1. 将查询提取为xml
2. 通过查看标签替换我想要的对象
3. 使用 DBMS_METADATA.CONVERT 将 xml 转换回 ddl 语句。

但正如我所说，xml 本身不包含 sql 查询结构。有人知道如何获取结构化为“xml 对象”或其他结构化形式的 sql 语句吗？

Answer 1

这取决于您的条件，但一种方法是使用 conditional compilation ;尽管这意味着您必须检查所有代码，但它在某些情况下非常有用。不是 100% 清楚这是否对您有帮助；您首先提到“以编程方式”，但接着说您想要搜索字符串，在这种情况下可能会这样。

条件编译使用一组静态 bool 语句在编译时更改源代码。编译器标志以 $$ 为前缀，您可以控制结构 $IF-$THEN-$ELSE 发生的情况。

例如，如果您想更改您从过程中选择的表，可能看起来像这样:

SQL> create or replace procedure do_something (
  2       p_curs out sys_refcursor ) is
  3
  4  begin
  5
  6     $if $$right_schema $then
  7        open p_curs for
  8          select * from user_tables;
  9     $else
 10        open p_curs for
 11          select * from all_tables;
 12     $end
 13
 14  end;
 15  /

Procedure created.

如果您使用 DBMS_METADATA 查看程序那么它看起来就像上面那样；然而，DBMS_PREPROCESSOR你可以看到它是如何编译的:

SQL> begin
  2    dbms_preprocessor.print_post_processed_source (
  3      object_type => 'PROCEDURE',
  4      schema_name => user,
  5      object_name => 'DO_SOMETHING');
  6  end;
  7  /
procedure do_something (
p_curs out sys_refcursor ) is
begin
open p_curs for
select * from all_tables;
end;

PL/SQL procedure successfully completed.

这是因为编译器标志 $$right_schema 默认为 false。使用 ALTER PROCEDURE我们可以将标志设置为 true 的语句:

alter procedure do_something compile plsql_ccflags = 'right_schema:TRUE';

打印出处理过的源现在显示如下:

SQL> begin
  2    dbms_preprocessor.print_post_processed_source (
  3      object_type => 'PROCEDURE',
  4      schema_name => user,
  5      object_name => 'DO_SOMETHING');
  6  end;
  7  /
procedure do_something (
p_curs out sys_refcursor ) is
begin
open p_curs for
select * from user_tables;
end;

PL/SQL procedure successfully completed.

Oracle Base 有 a useful article还有一些例子。