Prevent output of SPOOL from being wrapped
Solution 1
How about using word_wrapped?
SET trimspool ON
SET heading off
SET linesize 300
SET echo off
SET pages 999
SET long 90000
set termout off
column txt format a121 word_wrapped
Col object_type format a10000
Col object_name format a10000
Col owner format a10000
spool export.out
SELECT DBMS_METADATA.GET_DDL(object_type, object_name, owner)txt
FROM all_OBJECTS
WHERE OWNER = 'DMALM'
and object_type not like '%PARTITION'
and object_type not like '%BODY'
and object_type not like '%LOB';
spool off
quit
Solution 2
You need to also do:
SET longchunksize 90000
The default width of datatype columns is the width of the column in the database. The column width of a
LONG
,BLOB
,BFILE
,CLOB
,NCLOB
orXMLType
defaults to the value ofSET LONGCHUNKSIZE
orSET LONG
, whichever is the smaller.
You're setting LONG
already, but LONGCHUNKSIZE
is still at its default value of 80, so you need to increase that to match. You can see all your current settings with show all
.
This preserves the line breaks and indentation applied by default.
Solution 3
Sounds like you may want to try:
set longchunksize 100
or equivalent. Experiment with the number see if it helps.
Source Oracle Docs
Filippo
Updated on June 21, 2022Comments
-
Filippo almost 2 years
I'm trying to generate all DDLs for objects in a Database using the SPOOL command in SQLPLUS:
SET trimspool ON SET wrap off SET heading off SET linesize 300 SET echo off SET pages 999 SET long 90000 Col object_type format a10000 Col object_name format a10000 Col owner format a10000 spool export.out SELECT DBMS_METADATA.GET_DDL(object_type, object_name, owner) FROM all_OBJECTS WHERE OWNER = 'DMALM' and object_type not like '%PARTITION' and object_type not like '%BODY' and object_type not like '%LOB'; spool off quit
But the output file I get is cut at col #80. How can I prevent the output file from being wrapped?
-
Alex Poole over 9 yearsThat works but loses the indentation applied by default (with 'pretty' turned on in the DBMS_METADATA transformation parameters).