CLOB displaying incorrectly in sqlplus

From: <alanpo_at_earthlink.net>
Date: Tue, 18 Jan 2000 18:49:01 GMT
Message-ID: <862cio$ita$1@nnrp1.deja.com>

I am trying to do a report/query to display rows in sqlplus which contain a CLOB field. However, after a shorter (< 50 chars. or so) CLOB is displayed from the query, subsequent CLOBs do not display correctly. They only display about 10 characters per line before wrapping to the next line.

This sounds like a bug to me. Any suggestions?

I strongly prefer not to resort to displaying the CLOB values using DBMS_OUTPUT(..) and PL/SQL procedures, but rather just continue to use plain SQL.

I am using Oracle 8.0.5 on Solaris x86 2.6.

Here is an SQL script and the resulting output, which demos this behavior:

set linesize 80
set pagesize 1024
set long 512
set longchunksize 512

column msg_text heading 'CLOB field' format a512 word_wrapped

CREATE TABLE test_lob
(

msg_text CLOB
);

INSERT INTO test_lob
(msg_text)

VALUES (
'1 This is the first record in the msg_text field for this table. It is some what more than 80 chars long in order to test lob field display in sqlplus '
);

INSERT INTO test_lob
(msg_text)

VALUES (
'2 This is the second record in the msg_text field for this table. It is somewhat more than 80 chars long in order to test lob field display in sqlplus. It is more than 2 lines long. ' );

INSERT INTO test_lob
(msg_text)

VALUES (
'3 Third record'
);

INSERT INTO test_lob
(msg_text)

VALUES (
'4 This is the forth record in the msg_text field for this table. It is somewhat more than 80 chars long in order to test lob field display in sqlplus '
);

select * from test_lob;

quit;

The script's output is:

SQL*Plus: Release 8.0.5.0.0 - Production on Tue Jan 18 13:7:53 2000

Connected to:
Oracle8 Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production

Table dropped.

Table created.

1 row created.

CLOB field

1 This is the first record in the msg_text field for this table. It is somewhat more than 80 chars long in order to test lob field display in sqlplus

2 This is the second record in the msg_text field for this table. It is somewhat more than 80 chars long in order to test lob field display in sqlplus. It is more than 2 lines long.

3 Third record
4 This is the forth record i
n the msg_text
field for thi
s table. It is
somewhat more
than 80 chars
long in order
to test lob f
ield display i
n sqlplus

Disconnected from Oracle8 Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Jan 18 2000 - 12:49:01 CST