SqlPlus 中行显示不全的问题（linesize和long的区别）

现象：在使用dbms包中调用get_ddl函数查询的时候，sqlplus显示的结果不全。

SYS@ORCL>/DBMS_METADATA.GET_DDL(UPPER('VIEW'),UPPER('V_$PARAMETER'),UPPER('SYS'))--------------------------------------------------------------------------------  CREATE OR REPLACE FORCE VIEW "SYS"."V_$PARAMETER" ("NUM", "NAME", "TYPE", "VA

解决方法：

1.查看官方文档中，说明默认页尺寸中每行是80个字符。

SQL*Plus User's Guide and Reference

6 SQPUG016Formatting SQL*Plus Reports

Setting Page Dimensions

Typically, a page of a report contains the number of blank line(s) set in the NEWPAGE variable of the SET command, a top title, column headings, your query results, and a bottom title. SQL*Plus displays a report that is too long to fit on one page on several consecutive pages, each with its own titles and column headings. The amount of data SQL*Plus displays on each page depends on the current page dimensions.

The default page dimensions used by SQL*Plus are shown underneath:

• number of lines before the top title: 1

• number of lines per page, from the top title to the bottom of the page: 14

• number of characters per line: 80

2.将结果复制出来查看，实际上查看结果算最后的换行符恰好是80个字符。验证了官方文档中的说法。

  CREATE OR REPLACE FORCE VIEW "SYS"."V_$PARAMETER" ("NUM", "NAME", "TYPE", "VA

3.官方的解决方法是set linesize。

You may wish to reduce the line size to center a title properly over your output, or you may want to increase line size for printing on wide paper. You can change the line width using the LINESIZE variable of the SET command:

SET LINESIZE number_of_characters

4.但是测试并不有效,继续向下刨。

SYS@ORCL>set linesize 900            SYS@ORCL>/DBMS_METADATA.GET_DDL(UPPER('VIEW'),UPPER('V_$PARAMETER'),UPPER('SYS'))--------------------------------------------------------------------------------  CREATE OR REPLACE FORCE VIEW "SYS"."V_$PARAMETER" ("NUM", "NAME", "TYPE", "VA

5.在官方文档，重新查询此函数的定义，发现返回的是CLOB类型而不是字符型，所以setlinesize对clob类型的返回值无效。

PL/SQL Packages and Types Reference
87 DBMS_METADATA

GET_xxx Functions

DBMS_METADATA.GET_DDL (object_type     IN VARCHAR2,name            IN VARCHAR2,schema          IN VARCHAR2 DEFAULT NULL,version         IN VARCHAR2 DEFAULT 'COMPATIBLE',model           IN VARCHAR2 DEFAULT 'ORACLE',transform       IN VARCHAR2 DEFAULT 'DDL')RETURN CLOB;

6.在官方中查询SET的使用方法

SQL*Plus User's Guide and Reference

SET System Variable Summary

其中linesize是设置字符型的，long是设置clob等类型的

SET LIN[ESIZE] {80 | n}

Sets the total number of characters that SQL*Plus displays on one line before beginning a new line.

SET LONG {80 | n}

Sets maximum width (in bytes) for displaying LONG, BLOB, BFILE, CLOB, NCLOB and XMLType values; and for copying LONG values.

7.最后设置宽度查看返回结果，问题解决！

SYS@ORCL>set long 6666SYS@ORCL>/DBMS_METADATA.GET_DDL(UPPER('VIEW'),UPPER('V_$PARAMETER'),UPPER('SYS'))--------------------------------------------------------------------------------  CREATE OR REPLACE FORCE VIEW "SYS"."V_$PARAMETER" ("NUM", "NAME", "TYPE", "VALUE", "DISPLAY_VALUE", "ISDEFAULT", "ISSES_MODIFIABLE", "ISSYS_MODIFIABLE", "ISINSTANCE_MODIFIABLE", "ISMODIFIED", "ISADJUSTED", "ISDEPRECATED", "DESCRIPTION", "UPDATE_COMMENT", "HASH") AS  select "NUM","NAME","TYPE","VALUE","DISPLAY_VALUE","ISDEFAULT","ISSES_MODIFIABLE","ISSYS_MODIFIABLE","ISINSTANCE_MODIFIABLE","ISMODIFIED","ISADJUSTED","ISDEPRECATED","DESCRIPTION","UPDATE_COMMENT","HASH" from v$parameter

结论：涉及到宽度调整的话 ，先查看结果集的类型，再依据类型进行调整。

SET LINESIZE ：characters

SET LONG ：LONG, BLOB, BFILE, CLOB, NCLOB and XML