在Oracle的SQLPLUS下显示CLOB中的内容

最新推荐文章于 2024-04-15 20:58:23 发布

原创最新推荐文章于 2024-04-15 20:58:23 发布 · 2.9k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#管理 #数据 #oracle

oracle 专栏收录该内容

50 篇文章

订阅专栏

本文介绍了SQL*Plus中SETLONG命令的使用方法及其对LONG类型数据处理的影响。通过调整SETLONG的值可以改变LONG数据的显示长度，对于处理大型文本数据非常有用。文章还提供了设置示例及使用DBMS_METADATA.GET_DDL过程获取对象定义的例子。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SET LONG {80 | n}

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

Querying LONG columns requires enough local memory to store the amount of data specified by SET LONG, irrespective of the value of the SET LONGCHUNKSIZE command. This requirement does not apply when querying LOBs.

It is recommended that you do not create tables with LONG columns. LONG columns are supported only for backward compatibility. Use LOB columns (BLOB, BFILE, CLOB, NCLOB) instead. LOB columns have fewer restrictions than LONG columns and are still being enhanced.

The maximum value of n is 2,000,000,000 bytes. It is important to check that the memory required by your SET LONG command is available on your machine, for example:

SET LONG 2000000000

assumes that available RAM (random access memory) on the machine exceeds 2 gigabytes.

Example

To set the maximum number of bytes to fetch for displaying and copying LONG values, to 500, enter

SET LONG 500

The LONG data will wrap on your screen; SQL*Plus will not truncate until the 501st byte. The default for LONG is 80 bytes.

以下面常用的pl/sql package为例：

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; --返回CLOB类型的值

获取视图EXU9DEFPSWITCHES的定义：

[icdlog]$sqlplus /nolog

SQL*Plus: Release 9.2.0.8.0 - Production on Wed Nov 19 17:50:44 2014

SQL> conn / as sysdba
Connected.
SQL> select dbms_metadata.get_ddl('VIEW','EXU9DEFPSWITCHES') from dual;

DBMS_METADATA.GET_DDL('VIEW','EXU9DEFPSWITCHES')
--------------------------------------------------------------------------------

CREATE OR REPLACE FORCE VIEW "SYS"."EXU9DEFPSWITCHES" ("COMPFLGS", "NLSLENSEM --只显示了一部分

SQL> show long
long 80
SQL> set long 102400
SQL> select dbms_metadata.get_ddl('VIEW','EXU9DEFPSWITCHES') from dual;

DBMS_METADATA.GET_DDL('VIEW','EXU9DEFPSWITCHES')
--------------------------------------------------------------------------------

CREATE OR REPLACE FORCE VIEW "SYS"."EXU9DEFPSWITCHES" ("COMPFLGS", "NLSLENSEM"
SELECT a.value, b.value
        FROM    sys.v$parameter a, sys.v$parameter b
        WHERE   a.name = 'plsql_compiler_flags' AND
                b.name = 'nls_length_semantics' --完整定义

SQL>