Prevent output of SPOOL from being wrapped

Question

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?

Are you sure the data in the file is wrapped, rather than your text editor wrapping it? — Alex Poole, Aug 05 '14 at 15:41
@AlexPoole I'm using notepad++ to open the file and the wrap option is turned off.. — Filippo, Aug 05 '14 at 15:43
You are keeping the linesize to 300 bytes but keeping length of object_type,object_name,owner to ten thousand (i.e a10000). make them to a30 as none of them can be larger than that... plus you need to use DBMS_METADATA.SET_TRANSFORM_PARAM to set the various parameters so that your output is as per required format. — narendra, Aug 05 '14 at 15:50
Those `col` settings are irrelevant as those columns aren't included in the result set. — Alex Poole, Aug 05 '14 at 16:04

Alex Poole · Answer 1 · 2014-08-05T16:05:59.710

You need to also do:

SET longchunksize 90000

As the documentation says:

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 or XMLType defaults to the value of SET LONGCHUNKSIZE or SET 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.

score 1 · Accepted Answer · answered Aug 05 '14 at 15:56

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

That works but loses the indentation applied by default (with 'pretty' turned on in the DBMS_METADATA transformation parameters). — Alex Poole, Aug 05 '14 at 16:01

score 1 · Answer 3 · answered Aug 05 '14 at 15:58

1

Sounds like you may want to try:

set longchunksize 100

or equivalent. Experiment with the number see if it helps.

Source Oracle Docs

answered Aug 05 '14 at 15:58

gnuchu

1,496
13
21

score -1 · Answer 4 · answered Apr 09 '22 at 03:58

The following works. You can download all SPs under one schema. You can also download one SP at a time.

CREATE or replace DIRECTORY DIR_DDL AS '/dir_name';
grant read, write on directory DIR_DDL to user_name;


-- ====================================================
-- NOTE:  Need to use the object owner user, not SYS.
-- ====================================================

DECLARE
  v_ddl_clob  CLOB;

BEGIN

  FOR c IN ( SELECT object_name, object_type
               FROM dba_objects
              WHERE object_type IN ('PROCEDURE')
                AND OWNER = 'SYS'
                AND object_name = 'SP_NAME' )
  LOOP
--  You want "/" in a new line.  Otherwise, it will not work
    v_ddl_clob := dbms_metadata.get_ddl(c.object_type, c.object_name) ||'
    /';

    -- write to SQL directory :
    dbms_xslprocessor.clob2file(v_ddl_clob, 'DIR_DDL', c.object_name||'.sql');

  END LOOP;

END;
/

The OP specifically asked about spool, which writes to files on the client machine. This will write to files on the server. Useful of course, but not a correct/relevant answer to this specific question. — Alex Poole, Apr 09 '22 at 07:09

Prevent output of SPOOL from being wrapped

4 Answers4