對應定義結構獲取

函式：w_func_getobjectddl(object_type varchar2, object_name varchar2)
引數說明：
object_type指物件型別，如INDEX,TABLE等；
object_name指物件的名稱。
目前只對索引型別的比較有效，對如定義欄位比較多的表等物件還不能很好查詢。
以後進行最佳化。
幾個重要資訊：
1、管道輸出，返回的結果是一個結果集，需要用table函式來轉換輸出；
2、對換行符(chr(3)/chr(10))的替換等內容；
3、返回的型別type_xmlstring是單獨定義的一個型別，是一個字串陣列。


使用方式：
Connected to Oracle9i Enterprise Edition Release 9.2.0.8.0
Connected as dvbcetus_sup
 
SQL> select * from table(w_func_getobjectddl('INDEX', 'IDX_REG_AREA'));
 
COLUMN_VALUE
--------------------------------------------------------------------------------
--IDX_REG_AREA
  create index dvbcetus_sup.idx_reg_area on dvbcetus_sup.tmp_tool_regionaccinfo
pctfree 10 initrans 2 maxtrans 255 nologging
storage(initial 81920 next 81920 minextents 1 maxextents 2147483645  pctincrease
tablespace indx;
 
函式體：
create or replace function w_func_getobjectddl(object_type varchar2,  object_name varchar2)
return type_xmlstring pipelined as
/*Created by
* 提取物件定義資訊
*/
     v_rstr  varchar2(1000);
     v_len   number(4) := 1;
     v_stpos number(4) := 0;
     v_ptpos number(4) := 0;
     v_tbpos number(4) := 0;
     v_strt  number(4) := 1;
     v_sublen number(4) := 0;
     v_head varchar2(255);
     v_mid1 varchar2(255);
     v_mid2 varchar2(255);
     v_tail varchar2(255);
     v_filterstr varchar2(1000);
     v_userprefix varchar2(30) := '"'; --指定要過濾的字串
     v_clob clob;

Begin
      begin
           --獲取物件的定義資訊
           v_clob := dbms_metadata.get_ddl(object_type, object_name);
           v_len := dbms_lob.getlength(v_clob);

           --讀取clob內容到字串變數中
           dbms_lob.read(v_clob, v_len, v_strt, v_rstr);

           v_filterstr := replace(replace(replace(v_rstr, chr(3)), chr(10)), v_userprefix); --過濾掉換行符
           v_filterstr := lower(v_filterstr);
           v_len := length(v_filterstr);
           v_ptpos := instr(v_filterstr, 'pctfree'); --在pctfree處進行截斷
           v_stpos := instr(v_filterstr, 'storage') ; --在storage處進行截斷
           v_tbpos := instr(v_filterstr, 'tablespace'); --在tablespace處進行截斷

           v_head := substr(v_filterstr,1, v_ptpos - 1);
           v_mid1 := substr(v_filterstr, v_ptpos, v_stpos - v_ptpos - 1);
           v_mid2 := substr(v_filterstr, v_stpos, v_tbpos - v_stpos - 1);
           v_tail     := substr(v_filterstr, v_tbpos, v_len - v_tbpos - 1) || ';';

           pipe row('--' || object_name);
           pipe row(v_head);
           pipe row(v_mid1);
           pipe row(v_mid2);
           pipe row(v_tail);
      exception when others then
                 dbms_output.put_line('Error: ' || object_name);
      end;
      return;
End w_func_getobjectddl;