利用dbms_metadata.get_ddl生成對應定義語句

這個話題不僅在網上已經被重複過不知道多少次了，在我的這個blog上也已
經是第二次來實現這個功能了。第一次的演算法比較土，就是找到指定的關鍵字
就換行，如：tablespace/storage等。這種方式很大的缺點就是如果是表定義
語句，而且是有很多欄位的（超過255字元數）的，就會出現錯誤。而且這個
錯誤還沒有很好的辦法來解決。
目前從dbms_metadata.get_ddl中獲取ddl語句最麻煩的就是如何在合適的
位置換行。如果一次輸出肯定會超出255的每行最大輸出字元數，會丟擲異常。
如果按照某個空格截斷，其實也很不好判斷到底在哪裡截。而且截出來的效
果100％的情況是非常差，也沒什麼可讀性了。

這次實現的方式應該說是靈機一動想出來的。也不用很多的步驟。
目前發現的除了對最大字元數有限制外（4000字元），其他都可正常使用。
我測試輸出了前面的254個字元，發現其實他本身的語句是有換行的。這就
給我提示。何不按他自己的換行符來實現分段擷取呢。
Oracle中的換行符可透過chr(10)來定位：
v_pos := instr(v_result, chr(10), i);
依次找到v_pos和之前的v_pos(v_tpos)，就可以定位兩個換行符之間的一行了
        if ( v_tpos <> v_pos ) then
            v_temp := lower(substr(v_result, v_tpos, v_pos - v_tpos));
            v_tpos := v_pos;
        end if;

最新版本，支援超出4000字元範圍的大結構定義（如package等）：
create or replace function w_func_getobjectddl_lob(
                            object_type varchar2,
                            object_name varchar2)
return tbl_varchar2 pipelined  as
/*Created by
    v_result    varchar2(4000);
    v_temp      varchar2(2000);
    v_pos       pls_integer := 0;
    v_tpos      pls_integer := 0;
   
    v_clob      clob;
    v_bufsize   pls_integer := 4000;
    v_cloblen   pls_integer := 0;
    v_nextpos   pls_integer := 1;
    v_prevpos   pls_integer := 1;
    v_initcount pls_integer := 50;
    v_nextcount pls_integer := v_initcount;
    v_offset    pls_integer := 1;
begin
    dbms_output.enable(9999999999999);
    v_clob := dbms_metadata.get_ddl(object_type, object_name);
    v_cloblen := dbms_lob.getlength(v_clob);
    v_bufsize := dbms_lob.instr(v_clob, chr(10), 1, v_initcount);

    while v_nextpos <> 0 loop
        v_nextpos := dbms_lob.instr(v_clob, chr(10), 1, v_nextcount);
        v_bufsize := (case when v_nextpos = 0 then v_cloblen else v_nextpos end) - v_prevpos;

        dbms_lob.read(v_clob, v_bufsize, v_offset, v_result);

        v_result := v_result || chr(10);
        v_tpos := 0;
        for j in 1..v_bufsize loop
            v_pos := instr(v_result, chr(10), j);
            if ( v_tpos <> v_pos ) then
                v_temp := lower(substr(v_result, v_tpos + 1, v_pos - v_tpos - 1));
                pipe row(v_temp);
                v_tpos := v_pos;
            end if;
        end loop;
        v_prevpos   := v_nextpos;
        v_offset    := v_offset + v_bufsize;
        v_nextcount := v_nextcount + v_initcount;
    end loop;

    return;
exception when others then
    pipe row(sqlerrm);
    return;
end w_func_getobjectddl_lob;

下面是實現的原始函式，透過管道來返回指定表的定義語句：
create or replace function w_func_getobjectddl(
                            object_type varchar2,
                            object_name varchar2)
return tbl_varchar2 pipelined  as
/*Created by

    v_result  varchar2(4000);
    v_temp    varchar2(255);
    v_pos     pls_integer := 0;
    v_tpos    pls_integer := 0;
begin
    dbms_output.enable(9999999999999);
    v_result := dbms_metadata.get_ddl(object_type, object_name);
    for i in 1..length(v_result) loop
        v_pos := instr(v_result, chr(10), i);
        if ( v_tpos <> v_pos ) then
            v_temp := lower(substr(v_result, v_tpos, v_pos - v_tpos));
            pipe row(v_temp);
            v_tpos := v_pos;
        end if;
    end loop;
    return;
end;
這裡需要有個陣列型別來支援管道的返回資訊：
create or replace type tbl_varchar2 as table of varchar2(255);
函式編譯成功後，即可透過下面的方式來獲取物件定義語句：
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as customer
 
SQL> select * from table(w_func_getobjectddl('TYPE', 'REC_INPUT_ASW'));
 
COLUMN_VALUE
--------------------------------------------------------------------------------
 
  create or replace type "customer"."rec_input_asw" as object(
 
 
    c_userinfoid varchar2(32),
 
 
    c_answerid varchar2(32),
 
 
    c_subjectid varchar2(32),
 
 
    c_optionid varchar2(32),
 
 
    c_continuation varchar2(1000) --答題內容
 
 
)
 
 
11 rows selected
 
SQL>
SQL> select * from table(w_func_getobjectddl('TABLE', 'TEST'));
 
COLUMN_VALUE
--------------------------------------------------------------------------------
 
  create table "customer"."test"
 
 
   ( "name" varchar2(255),
 
 
 "id" number(18,0)
 
 
   ) pctfree 10 pctused 40 initrans 1 maxtrans 255 nocompress logging
 
 
  storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645
 
 
6 rows selected
 
SQL>

後面考慮繼續最佳化這個函式功能。
下面又做了部分最佳化：
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as customer
 
SQL> select * from table(w_func_getobjectddl('TABLE', 'TEST'));
 
COLUMN_VALUE
--------------------------------------------------------------------------------
  create table "customer"."test"
   ( "name" varchar2(255),
 "id" number(18,0)
   ) pctfree 10 pctused 40 initrans 1 maxtrans 255 nocompress logging
  storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645
  pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)
  tablespace "fund_table"
 
9 rows selected
 
SQL> select * from table(w_func_getobjectddl('TYPE', 'REC_INPUT_ASW'));
 
COLUMN_VALUE
--------------------------------------------------------------------------------
  create or replace type "customer"."rec_input_asw" as object(
    c_userinfoid varchar2(32),
    c_answerid varchar2(32),
    c_subjectid varchar2(32),
    c_optionid varchar2(32),
    c_continuation varchar2(1000) --答題內容
)
 
11 rows selected
 
SQL>

程式碼內容：
create or replace function w_func_getobjectddl(
                            object_type varchar2,
                            object_name varchar2)
return tbl_varchar2 pipelined  as
/*Created by
    v_result  varchar2(4000);
    v_temp    varchar2(255);
    v_pos     pls_integer := 0;
    v_tpos    pls_integer := 0;
begin
    dbms_output.enable(9999999999999);
    v_result := dbms_metadata.get_ddl(object_type, object_name);
    for i in 1..length(v_result) loop
        v_pos := instr(v_result, chr(10), i);
        if ( v_tpos <> v_pos ) then
            prinfo('v_tpos: ' || v_tpos || ' -> v_pos: ' || v_pos);
            v_temp := lower(substr(v_result, v_tpos + 1, v_pos - v_tpos - 1));
            pipe row(v_temp);
            v_tpos := v_pos;
        end if;
    end loop;
    return;
end;