在cursor游标中使用dbms_metadata.get_ddl提取依赖其它对象的父对象之相关性

--验证依赖其它对象的父对象,如其子对象未用dbms_metadata.get_ddl复制到目标库，会否在游标开启范围内导致后续的源库对象不能继续复制到目标库
如object_name还要引用其它的对象
--select dbms_metadata.get_ddl(v_object_type,v_tname,'E_CHANNEL') into v_text from dual;

--1,在scott用户构建要复制的存储过程(此用户为源库)，且此存储过程要引用测试表
    --先创建测试表
    create table t_oy(a int);
   
    --创建引用上述表的存储过程
    create or replace procedure proc_oy
    as
    v_cnt pls_integer;
    begin
    select a into v_cnt from t_oy;
    end;
   
    --创建另一个存储过程
    create or replace procedure proc_non
    as
    begin
    null;
    end;

--2,在tbl_bck的存储过程如下：
create or replace procedure proc_dep
as
cursor cur_test is select object_name,object_type,owner from dba_objects where object_name in ('PROC_OY','PROC_NON');
v_object_type dba_objects.object_type%type;
v_object_name dba_objects.object_name%type;
v_owner dba_objects.object_name%type;
v_text clob;
begin
open cur_test;
loop
fetch cur_test into v_object_name,v_object_type,v_owner;
exit when cur_test%notfound;
dbms_metadata.set_transform_param( dbms_metadata.SESSION_TRANSFORM,'TABLESPACE',false);
select dbms_metadata.get_ddl(v_object_type,v_object_name,v_owner) into v_text from dual;
v_text:=replace(v_text,v_owner,'tbl_bck');
execute immediate v_text;
end loop;
close cur_test;
end;

--存储过程编译正常，但运行报用户不存在，经分析，是dbms_metadata.get_ddl的参数schema值必须是大写，真是鬼鬼
SQL> create or replace procedure proc_dep
  2  authid current_user is
  3  cursor cur_test is select object_name,object_type,owner from dba_objects where wner='SCOTT' and object_name in ('PROC_OY','PROC_NON');
  4  v_object_type dba_objects.object_type%type;
  5  v_object_name dba_objects.object_name%type;
  6  v_owner dba_objects.object_name%type;
  7  v_text clob;
  8  begin
  9  open cur_test;
 10  loop
 11  fetch cur_test into v_object_name,v_object_type,v_owner;
 12  exit when cur_test%notfound;
 13  dbms_metadata.set_transform_param( dbms_metadata.SESSION_TRANSFORM,'TABLESPACE',false);
 14  select dbms_metadata.get_ddl(v_object_type,v_object_name,v_owner) into v_text from dual;
 15  v_text:=replace(v_text,v_owner,'tbl_bck');
 16  execute immediate v_text;
 17  end loop;
 18  close cur_test;
 19  end;
 20  /
 
Procedure created
 
SQL> exec proc_dep;
 
begin proc_dep; end;
 
ORA-01435: user does not exist
ORA-06512: at "TBL_BCK.PROC_DEP", line 16
ORA-06512: at line 2

---经修正后运行正常，但仍报错，不过是另一个错误了。
SQL> create or replace procedure proc_dep
  2  authid current_user is
  3  cursor cur_test is select object_name,object_type,owner from dba_objects where wner='SCOTT' and object_name in ('PROC_OY','PROC_NON');
  4  v_object_type dba_objects.object_type%type;
  5  v_object_name dba_objects.object_name%type;
  6  v_owner dba_objects.object_name%type;
  7  v_text clob;
  8  begin
  9  open cur_test;
 10  loop
 11  fetch cur_test into v_object_name,v_object_type,v_owner;
 12  exit when cur_test%notfound;
 13  dbms_metadata.set_transform_param( dbms_metadata.SESSION_TRANSFORM,'TABLESPACE',false);
 14  select dbms_metadata.get_ddl(v_object_type,v_object_name,v_owner) into v_text from dual;
 15  v_text:=replace(v_text,v_owner,'TBL_BCK');
 16  execute immediate v_text;
 17  end loop;
 18  close cur_test;
 19  end;
 20  /
 
Procedure created
 
SQL> exec proc_dep;
 
Warning: PL/SQL procedure successfully completed with compilation errors
 
SQL> show err
No errors
 
SQL> select * from tab;
 
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------

--注意：依赖表的存储过程proc_oy状态为invalid
SQL> select * from user_objects;
 
OBJECT_NAME                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY  NAMESPACE EDITION_NAME
-------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------
PROC_OY                                                                                                              68402                PROCEDURE           2013/2/28 2 2013/2/28 21: 2013-02-28:21:41:41 INVALID N         N         N                  1
PROC_NON                                                                                                             68401                PROCEDURE           2013/2/28 2 2013/2/28 21: 2013-02-28:21:41:41 VALID   N         N         N                  1
PROC_DEP                                                                                                             68400                PROCEDURE           2013/2/28 2 2013/2/28 21: 2013-02-28:21:41:36 VALID   N         N         N                  1
 

--为了先执行依赖表的存储过程，修正了存储过程，添加了order by
SQL> create or replace procedure proc_dep
  2  authid current_user is
  3  cursor cur_test is select object_name,object_type,owner from dba_objects where wner='SCOTT' and object_name in ('PROC_OY','PROC_NON') order by object_name desc;
  4  v_object_type dba_objects.object_type%type;
  5  v_object_name dba_objects.object_name%type;
  6  v_owner dba_objects.object_name%type;
  7  v_text clob;
  8  begin
  9  open cur_test;
 10  loop
 11  fetch cur_test into v_object_name,v_object_type,v_owner;
 12  exit when cur_test%notfound;
 13  dbms_metadata.set_transform_param( dbms_metadata.SESSION_TRANSFORM,'TABLESPACE',false);
 14  select dbms_metadata.get_ddl(v_object_type,v_object_name,v_owner) into v_text from dual;
 15  v_text:=replace(v_text,v_owner,'TBL_BCK');
 16  execute immediate v_text;
 17  end loop;
 18  close cur_test;
 19  end;
 20  /
 
Procedure created

--再次运行，依旧报上述的错误
SQL> exec proc_dep;
 
Warning: PL/SQL procedure successfully completed with compilation errors

--查找2个对象,因proc_oy存储过程依赖于表t_oy,但此表未复制过来，故状态为invalid,导致后续的存储过程proc_non也未复制过来
SQL> select * from user_objects;
 
OBJECT_NAME                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY  NAMESPACE EDITION_NAME
-------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------
PROC_OY                                                                                                              68402                PROCEDURE           2013/2/28 2 2013/2/28 21: 2013-02-28:21:48:15 INVALID N         N         N                  1
PROC_DEP                                                                                                             68400                PROCEDURE           2013/2/28 2 2013/2/28 21: 2013-02-28:21:47:29 VALID   N         N         N                  1
 
小结：
      1,invalid状态的对象，开启游标时，在应用dbms_metadata.get_ddl会导致后续的对象不能继续复制
     

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-754996/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-754996/