现象:在使用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
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
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
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类型的返回值无效。
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的使用方法
SET System Variable Summary
Sets the total number of characters that SQL*Plus displays on one line before beginning a new line. |
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 6666
SYS@ORCL>/
DBMS_METADATA.GET_DDL(UPPER('VIEW'),UPPER('V_$PARAMETER'),UPPER('SYS'))
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."V_$PARAMETER" ("NUM", "NAME", "TYPE", "VAL
UE", "DISPLAY_VALUE", "ISDEFAULT", "ISSES_MODIFIABLE", "ISSYS_MODIFIABLE", "ISIN
STANCE_MODIFIABLE", "ISMODIFIED", "ISADJUSTED", "ISDEPRECATED", "DESCRIPTION", "
UPDATE_COMMENT", "HASH") AS
select "NUM","NAME","TYPE","VALUE","DISPLAY_VALUE","ISDEFAULT","ISSES_MODIFIAB
LE","ISSYS_MODIFIABLE","ISINSTANCE_MODIFIABLE","ISMODIFIED","ISADJUSTED","ISDEPR
ECATED","DESCRIPTION","UPDATE_COMMENT","HASH" from v$parameter
SET LINESIZE :characters
SET LONG :LONG, BLOB, BFILE, CLOB, NCLOB and XML