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.

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 summarizes 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