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

核心提示： 举个例子，假设一个方案需要克隆 SCOTT 的方案中的“FOO”的函数，使用动态SQL克隆数据库对象(3)，SCOTT 将拥有 CLONER 的一个副本并将 EXECUTE 权限授予允许克隆 SCOTT 的对象的用户，其它的用户可以发出以下 SQL*Plus 命令：

举个例子，假设一个方案需要克隆 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 所有者能够将对象从一个用户克隆到其他用户。