使用动态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;