| Set wrap off与clob | DBA之路：OoNiceDream |

Set wrap off与clob

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/01/set-wrap-off-clob.html

在本机使用sqlplus调用dbms_metadata.get_ddl时，发现查询不到结果：

SYS@TADBA> select dbms_metadata.get_ddl('TABLE','DUAL','SYS') from dual;
 
DBMS_METADATA.GET_DDL('TABLE','DUAL','SYS')
----------------------------------------------------------------------------
 
 
SYS@TADBA>

有点晕，记得以前还可以呀。使用telnet到一台机上执行，发现显示正常：

SQL> set long 1000
SQL> select dbms_metadata.get_ddl('TABLE','DUAL','SYS') from dual;
 
DBMS_METADATA.GET_DDL('TABLE','DUAL','SYS')
----------------------------------------------------------------------------
 
  CREATE TABLE "SYS"."DUAL"
   (    "DUMMY" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"
 
 
 
SQL>

想到是自己conn.sql脚本中设置sqlplus环境变量设置有问题，一一对比排查，最后没想到是set wrap off的引起的，真是狂汗：

SYS@TADBA> set wrap off
SYS@TADBA> select dbms_metadata.get_ddl('TABLE','DUAL','SYS') from dual;
 
DBMS_METADATA.GET_DDL('TABLE','DUAL','SYS')
----------------------------------------------------------------------------
 
 
SYS@TADBA> set wrap on
SYS@TADBA> select dbms_metadata.get_ddl('TABLE','DUAL','SYS') from dual;
 
DBMS_METADATA.GET_DDL('TABLE','DUAL','SYS')
----------------------------------------------------------------------------
 
  CREATE TABLE "SYS"."DUAL"
   (    "DUMMY" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"
 
 
 
SYS@TADBA> desc dbms_metadata
FUNCTION GET_DDL RETURNS CLOB
参数名称                       类型                    输入/输出默认值？
------------------------------ ----------------------- ------ --------
 OBJECT_TYPE                    VARCHAR2                IN
 NAME                           VARCHAR2                IN
 SCHEMA                         VARCHAR2                IN     DEFAULT
 VERSION                        VARCHAR2                IN     DEFAULT
 MODEL                          VARCHAR2                IN     DEFAULT
 TRANSFORM                      VARCHAR2                IN     DEFAULT