oracle 怎么下载clob,在Oracle的SQLPLUS下显示CLOB中的内容

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

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

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>