这个话题不仅在网上已经被重复过不知道多少次了，在我的这个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 Lyon@20090622*/
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 Lyon@20090622*/
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 Lyon@20090622*/
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;