Search notes:

Oracle DBMS_METADATA: Write selected DDL statements in a file with SQL*Plus' command SPOOL

The maximal length of an input line that SQL*Plus' can process is 3000 characters after variable substitution and 4999 characters before variable substitution.
Therefore, SQL*Plus cannot be used to process SQL statement to execute SQL scripts with SQL*Plus start or @ command if only one line exceeds this limit.
If trying to execute such scripts, SQL*plus will throw either the SP2-0027: Input is too long (> 4999 characters) - line ignored or SP2-0341: line overflow during variable substitution error message.
This page resulted from a few frustrating moments when I tried to use dbms_metadata.get_ddl together with SQL*Plus' spool because I experienced exactly those two errors.
The following steps is a summary on how it should be possible to extract DDL statements into an SQL script file and then to execute this script file to (re-)create the object.

Create a view with a very long text

In order to really go to the limits, I create a view that has a ridiculously long line: 
declare
   stmt varchar2(32000);
begin

   stmt := 'create or replace force view tq84_view_with_long_text as select 1 col_001';

   for i in 2 .. 999 loop
       stmt := stmt || ', ' || i || ' col_' || to_char(i, 'fm0009');
   end loop;

   stmt := stmt || ' from dual';

   execute immediate stmt;

end;
/
In fact, the length of the line is 13892 characters. 
select
   text_length,
   text
from
   user_views
where
   view_name = 'TQ84_VIEW_WITH_LONG_TEXT';
The length of this text is sufficient to challenge the limits of SQL*Plus.

Spooling the DDL statements

The following script is a variation of the script which I used in SQL*Plus to spool the result of dbms_metadata.get_ddl to a file: 
begin
--     dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'PRETTY'       , true );
       dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true );
end;
/

set    long           100000000
set    longchunksize  100000000
set    pagesize               0
set    linesize           32000
set    trimspool             on
set    termout              off

--
--     Prevent
--        SP2-0027: Input is too long (> 4999 characters) - line ignored and
--        SP2-0341: line overflow during variable substitution (>3000 characters at line 1)
--     when created file is spooled:
--
column stmt format a3000 word_wrapped

--
--    Specify name of SQL script file to be created:
--
spool view-definition.sql

select
  dbms_metadata.get_ddl('VIEW', 'TQ84_VIEW_WITH_LONG_TEXT') stmt
from
  dual;

spool off

set termout               on
Such a script file can be executed in SQL*Plus with the start or @ command: 
SQL> @create-ddl-file.sql
Executing create-ddl-file.sql creates the file view-definition.sql which contains the create view statement that (re-)creates the original tq84_view_with_long_text view.

(Re-) creating the original view

The original view can now be recreated by executing view-definition.sql in SQL*Plus: 
SQL> @view-definition

Index