使用动态SQL克隆数据库对象

越野车

　　首先给出一个警告：下面的技巧提示如果使用不小心会造成严重的安全漏洞。当你在一个非测试环境下使用这种方法之前，一定要完完全全地理解这样做的后果。 　　 　　如果你需要复制一个表并在（另一个用户名下的）另一个方案（schema）制作它的一个副本，那么你可以使用 SQL*Plus 的 COPY 命令，或者使用简单的语句“create table foo as select * from other.foo;”，只要你在另一个用户的表上有足够的 select 权限。 　　 　　然而，如果你需要对任何其它数据库对象做同样的事情，比如包、过程、函数或视图，就没有这么简单的命令了。你需要手工找出其代码并在新实例中运行它。如果能够简单“克隆对象”并让另一个用户的对象出现在自己的实例中，那将是一件非常值得高兴的事。对于为开发过程创建测试方案来说，这一点非常有用。在这个例子中，我将创建一个包，使用该包可以对大多数对象进行克隆（具有一些限制）。 　　 　　要处理的主要问题是获得源代码。对象所有者通过视图USER_SOURCE 可以访问源代码。而对于其它用户，如果对象被授予了EXECUTE 权限给一个用户，那么这个用户就只能看到通过视图ALL_SOURCE 选出的源代码。我们可以通过一个过程来封装“give me the source for your object（给我你的对象的源代码）”请求： 　　 　　create or replace procedure get_source 　　( 　　p_type　　　　　varchar2, 　　p_name　　　　　varchar2, 　　p_cursor out　　sys_refcursor 　　) 　　as 　　begin 　　open p_cursor for 　　select text from user_source 　　where type = upper(p_type) and name = p_name 　　order by line; 　　end get_source; 　　/ 　　show errors; 　　 　　注意，我没有用“UPPER(name)”。这就意味着你必须匹配这个存储过程的字母大小写。Java 存储过程使用很多大小写混合的名字。 　　 　　如果这个过程是由对象所有者所有的，那么那个对象的源代码就可以通过一个 REF CURSOR 变量导出。如果这个过程被授予了其他用户 EXECUTE 权限，那么这个用户将能够调用这个过程并查看任何数据库对象的源代码——即使是那些没有授权给他们的对象和那些在 ALL_SOURCE 中不给出的对象，比如 TYPE 声明。为了说明这种方法可行，请尝试在 SQL*Plus 中输入以下代码： 　　 　　SQL> connect scott/tiger 　　SQL> create function foo return varchar2 as begin return 'hello world'; end; 　　SQL> / 　　SQL> @get_source.sql 　　SQL> variable c refcursor; 　　SQL> exec get_source('FUNCTION','FOO',:c); 　　SQL> print c 　　 　　有了从对象所有者手中得到的源代码，调用者就可以在创建自己的方案中创建对象了。我们需要动态 SQL 来从文本字符串构建对象。另外一个需要处理的问题是一些数据库对象的源代码的长度可能会超过32767个字符，即超过 VARCHAR2 字符串的最大长度限制。这样就不能使用简单的 VARCHAR2 字符串来保存 SQL。在 Oracle 中有一个很少使用的变量DBMS_SQL.PARSE，可以使用它将源代码存储为一个由 VARCHAR2 字符行所组成的表中。这样的表可以用来存储超过32767长度限制的 SQL。（在实际的应用中，你可能还需要将任何大于256个字符的代码行包装起来，因为USER_SOURCE 最多只能存储4000行字符）。下面将其实现为一个带有命令行参数的 SQL*Plus 脚本的代码： 　　 　　declare 　　ipls_integer := 1; 　　l_source dbms_sql.varchar2s; 　　l_line varchar2(256); 　　l_cursorsys_refcursor; 　　c pls_integer; 　　r pls_integer; 　　begin 　　&1..get_source('&2','&3',l_cursor); 　　l_source(i) := 'create or replace'; 　　loop 　　fetch l_cursor into l_line; 　　exit when l_cursor%notfound; 　　i := i 1; 　　l_source(i) := l_line.text; 　　end loop; 　　close l_cursor; 　　if i = 1 then 　　raise_application_error(-20000,'object does not exist'); 　　end if; 　　c := dbms_sql.open_cursor; 　　dbms_sql.parse(c,l_source,1,l_source.count,true,dbms_sql.native); 　　dbms_sql.close_cursor(c); 　　end; 　　/ 　　show errors; 　　 　　举个例子，假设一个方案需要克隆 SCOTT 的方案中的“FOO”的函数。SCOTT 将拥有 CLONER 的一个副本并将 EXECUTE 权限授予允许克隆 SCOTT 的对象的用户。其它的用户可以发出以下 SQL*Plus 命令： 　　 　　SQL> connect ANOTHER USER 　　SQL> @clone SCOTT FUNCTION FOO 　　 　　这种做法可行，但是依然需要 SQL*Plus 会话和脚本。我想将所有东西都放在 SQL 中，以使得任何应用程序都可以执行这一功能。为了实现这一想法，我们需要将前面的 SQL*Plus 脚本包装成另外一个动态 SQL 语句，在这个语句中我们可以加上所有者的名称并将所有者和类型参数组合。可以用以下过程来实现： 　　 　　create or replace procedure clone_obj 　　( 　　p_owner　　　　 varchar2, 　　p_type　　　　　varchar2, 　　p_name　　　　　varchar2 　　) 　　authidcurrent_user 　　is 　　lf　　　　　char := chr(10); 　　begin 　　execute immediate 　　'declare' || lf 　　|| '　　ipls_integer := 1;' || lf 　　|| '　　l_source dbms_sql.varchar2s;' || lf 　　|| '　　l_line varchar2(4000);' || lf 　　|| '　　l_cursorsys_refcursor;' || lf 　　|| '　　c pls_integer;' || lf 　　|| '　　r pls_integer;' || lf 　　|| 'begin' || lf 　　|| '　　'||p_owner||'.get_source(:1,:2,l_cursor);' || lf 　　|| '　　l_source(i) := ''create or replace'';' || lf 　　|| '　　loop' || lf 　　|| '　　　　fetch l_cursor into l_line;' || lf 　　|| '　　　　exit when l_cursor%notfound;' || lf 　　|| '　　　　i := i 1;' || lf 　　|| '　　　　l_source(i) := l_line;' || lf 　　|| '　　end loop;' || lf 　　|| '　　close l_cursor;' || lf 　　|| '　　if i = 1 then' || lf 　　|| '　　　　raise_application_error(-20000,' 　　|| '''object does not exist'');' || lf 　　|| '　　end if;' || lf 　　|| '　　c := dbms_sql.open_cursor;' || lf 　　|| '　　dbms_sql.parse(c,l_source,1,l_source.count,' 　　|| 'true,dbms_sql.native);' || lf 　　|| '　　dbms_sql.close_cursor(c);' || lf 　　|| 'end;' || lf 　　using p_type,p_name; 　　end clone_obj; 　　/ 　　show errors; 　　 　　注意，使过程具有足够的权限来创建数据库对象，我必须添加AUTHID CURRENT_USER。现在你可以用任何能够调用 Oracle 存储过程的产品来调用这个过程。下面这个例子与前面的例子相同，只不过这个例子是写在 SQL*Plus 中的： 　　 　　SQL> @clone_obj 　　SQL> exec clone_obj('SCOTT','FUNCTION','FOO'); 　　 　　在这里会有一些安全问题，但是不多。只有被授予对 GET_SOURCE 有 EXECUTE 权限的用户才能读取他们常规情况下无法看到的对象的源代码。在理想情况下，你可以创建一个只包含“GET_SOURCE”和一组模板对象的用户。 　　 　　上面的程序还不完整，但是还是可以作为一个例子来用的。除了需要将4000个字符的源代码包装成256个字符长的目标行之外，可能还需要对其进行扩展以扫描对象的名称并插入一个所有者名称，以使得 DBA 所有者能够将对象从一个用户克隆到其他用户。 <