SQL*PLUS - SET LINESIZE doesn't have any effect in my script while it's spooling

Question

I'm using PL/SQL Developer version 11.0.6.1776

The following script, creates a folder named 'test' on the specified path and a subfolder within it, with the name of the table that is currently spooling to stored its metadata in that folder:

set pagesize 0
set long 90000
set linesize 10000

SET TERMOUT OFF
spool out.sql

select 'host mkdir ''C:\Users\personal\test'''||';'||chr(13)||chr(10)|| --Creating the new folder
       'host mkdir '||''''||'C:\Users\afpuchn\test\'||REPLACE(table_name, '$', '_')||''''||';'||chr(13)||chr(10)|| --Creating a subfolder
       'spool C:\Users\personal\test\'||REPLACE(table_name, '$', '_')||'\'||REPLACE(table_name, '$', '_')||'.txt'||chr(13)||chr(10)||
       'SELECT DBMS_METADATA.GET_DDL'||chr(13)||chr(10)||
       '(''TABLE'','''||table_name||''',''MTO_TABL'') '||chr(13)||chr(10)||
       'FROM DUAL;'||chr(13)||chr(10)|| --Extracting metadata
       'spool off' as cmd
FROM MTTO_TAB_EXIST_ALL tea
WHERE tea.MRK_DEL_PERM = 'Y'
AND tea.OWNER_NAME = 'MTO_TABL'
AND ROWNUM < = 5;

spool off

@OUT.SQL

exit

Whenever I run the script, the name I want to give to the file being stored in the created folder is incomplete or with the specified file type incomplete.

Here is an example of the script that returns:

host mkdir 'C:\Users\personal\test';
host mkdir 'C:\Users\personal\test\ED_EXTR_CSV_PRIM_DEA_PUBLI';
spool C:\Users\afpuchn\test\ED_EXTR_CSV_PRIM_DEA_PUBLI\ED_EXTR_CSV_PRIM_DEA_PU --The file name is incomplete
SELECT DBMS_METADATA.GET_DDL
('TABLE','ED_EXTR_CSV_PRIM_DEA_PUBLI','MTO_TABL')
FROM DUAL;
spool off

Because of this, the script cannot be stored in the given path even if I put a SET LINESIZE who doesn't works. Does my version of PL / SQL have any relation with this?

Answer 1

set linesize is not supported by the PL/SQL Developer command window, according to the manual. If you want to create and run SQL*Plus scripts, I strongly recommend you use the actual SQL*Plus from the command line, not an imitation.

I'm a big fan of using PL/SQL Developer for writing programs and worksheets, but it's not a good environment for scripting. Neither is SQL*Plus, but SQL*Plus is at least simple and compatible across a wide variety of platforms, which is why many DBAs use that tool to run scripts.