Why is SPOOL writing my output with LINESIZE set to 32767?

Question

I am trying to spool certain data, but for some reason it gets wrapped to the next line.

This is the script I am using to SPOOL (I am adding the full script with comments in case it has any significance)

SET SERVEROUTPUT ON FORMAT WRAPPED SIZE UNLIMITED
SET LONG 2000000000
SET LINESIZE 32767
SET VERIFY OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SET HEADING OFF
SET ECHO OFF
SET PAGESIZE 0
SET NEWPAGE NONE
SET TRIMSPOOL ON

/* -------------------------------------------------- COMMENT --------------------------------------------------
COMMENT1
COMMENT2
COMMENT3
COMMENT4
COMMENT5
*/
SPOOL "File_Name_MySYNONYM_SCRIPT-11111111.sql"
  select SCRIPT FROM MY_TABLE_2_UPDATE  where OBJECT_ID =11111;
SPOOL OFF

SCRIPT is a CLOB column and for the given ID contains:

  CREATE OR REPLACE PUBLIC SYNONYM "MY_SYNONYM_12345" FOR "MS1"."MY_OBJECT1_SETUP";

This is the Output I get in the spooled file:

  CREATE OR REPLACE PUBLIC SYNONYM "MY_SYNONYM_12345" FOR "MS1"."MY_OBJECT1_SETU
P";

You can see the P"; gets wrapped to the second line. The line is actually wrapped, it is not my editor (Notepad++) that is showing it wrong, I have "Word Wrap" deactivated.

I don't know what other SQL PLUS parameter I need to set to fix this issue.

Answer 1

Adding my comment as an answer as it seemed to help.

Try adding this after the SET command and before the SPOOL: "COLUMN SCRIPT FORM A3000". If sqlplus defaults the column SCRIPT to say 80 it will wrap.

However, reading further it seems you have other formatting issues, and also it seems the data is from DBMS_METADATA.GET_DLL.

I have written many tools (going back to v7) to extract DDL to scripts so that schemas can be rebuilt, and although DBMS_METADATA.GET_DDL is a huge help I still find it doesn't quite do the whole thing as neatly as you would like. So be warned. My scripts combine GET_DDL and some custom extract queries to get the nice clean usable scripts.

To remove extraneous line feeds, try this is your session:

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',false);

Documentation for this is here