CODEWITHSUNDEEP
oracle-databasesqlplusspool
Rishi Deorukhkar
Rishi Deorukhkar

Reputation: 189

Cannot SPOOL long header in a file

I am trying to spool a header with 208 fields in a file

  set feedback off
  set verify off
  set head off
  set echo off
  set pages 0
  set trimspool on
  SET LINESIZE 500

spool ${FILE_NAME}

select 'Field1,Field2,.........Field208'
from dual;

 SPOOL OFF
 exit

I'm getting the following error

SP2-0027: Input is too long (> 2499 characters) - line ignored

How do I achieve the task ??

Upvotes: 0

Views: 789

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

SP2-0027: Input is too long (> 2499 characters) - line ignored

You have hit a limitation of SQL*Plus, which has an absolute limit on the length of line of 2,499 characters.

The only way to do it in SQL*Plus is to have line breaks in your SQL statement instead of a single line and concatenate each line with the previous one.

For example,

Change this:

SQL> SELECT 'lalitkumar' NAME from dual;

NAME
----------
lalitkumar

To this:

SQL> select 'lalit'
  2  ||'kumar' name from dual;

NAME
----------
lalitkumar

SQL>

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191265

You need to split your string literal into two or more lines, to avoid the SQL*Plus command line limit (which is nothing to do with spooling). You can then concatenate the separate string literals together to get the output on a single line:

select 'Field1,Field2,.........Field99'
    || 'Field101,Field102,.........Field199'
    || 'Field201,Field202,.........Field208'
from dual;

How may times you need to split the string, and where, depends on your actual values.

You will also need to set your linesize much higher than the 500 you currently have, or it will wrap the output onto several lines anyway; it needs to be at least 2500 since you're already reaching at least that, but probably quite a bit more - again depending on your actual data. Maybe you meant 5000...

Upvotes: 2

Related Questions