I noticed a side effect of SET LONGC today…
The default setting for LONG and LONGC is 80. This is quite annoying when you SELECT TEXT FROM ALL_VIEWS as it truncates the text to 80.
So why not setting it to the maximum?
Let’s first demo the usage of LINESIZE, LONG and LONGCHUNKSIZE
SQL> create view v as select
'12345678910111213141516171819202' x
from dual;
View created.
SQL> set lin 80 long 20 longc 20
SQL> select text from user_views where view_name='V';
TEXT
--------------------
select '123456789101
SQL> set lin 80 long 1000000000 longc 20
SQL> select text from user_views where view_name='V';
TEXT
--------------------
select '123456789101
11213141516171819202
' x from dual
SQL> set lin 80 long 1000000000 longc 1000000000
SQL> select text from user_views where view_name='V';
TEXT
-----------------------------------------------------
select '12345678910111213141516171819202' x from dual
When I use a LONG setting smaller than the length of the TEXT column, I got it truncated. When I use a huge LONG setting but a LONGCHUNKSIZE setting smaller than the length of the TEXT column, I got it wrapped. When both are huge, it seems I am getting the expecting result. So why not setting SET LONG 2000000000 LONGC 2000000000 in your login.sql ?
Have a look :
SQL> SET LONG 10 LONGC 10 LIN 80 TIMI ON
SQL> SELECT TO_CLOB('X') FROM DUAL;
TO_CLOB('X
----------
X
Elapsed: 00:00:00.01
SQL> SET LONG 1000000 LONGC 1000000 LIN 80 TIMI ON
SQL> SELECT TO_CLOB('X') FROM DUAL;
TO_CLOB('X')
------------
X
Elapsed: 00:00:00.00
SQL> SET LONG 1000000000 LONGC 1000000000 LIN 80 TIMI ON
SQL> SELECT TO_CLOB('X') FROM DUAL;
TO_CLOB('X')
------------
X
Elapsed: 00:00:02.06
2 seconds to select 1 character from dual !
I also tried to set it to 2000000000 but since I do not have 2Gb of free memory my notebook started swapping so badly I could not even move my mouse 🙁
A nice observation.
I managed to fix the clob display problem by setting column format.
column my_col format a500000000
“set LONGCHUNKSIZE” is not needed after setting column format. In my test “set LONGCHUNKSIZE” eats up the memory in 11g (not in 10g) when the value is too high.
indeed, it is probably better than longc, but I could not format to anything higher than 60000.
SQL> col x for a500000000
SP2-0246: Illegal FORMAT string "a500000000"
SQL> col x for a60001
SP2-0246: Illegal FORMAT string "a60001"
SQL> col x for a60000
And it is documented : A COLUMN format can be up to a maximum of 60000 per row
sqlplus doc
You are right, “column my_col format a500000000” doesn’t work indeed, my error output was suppressed by setting “SET TERMOUT OFF;”.
Thanks for pointing it out.
In this case, it seems single line size for a clob is limited to be 32767 by using column format:
set long 2000000000;
set linesize 32767;
column COL format A60000