在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會導致後續的物件不能繼續複製