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

by Snehashish Ghosh

Scenario


 When we try to execute a script that has more than 2499 characters in it sqlplus throws an error

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

insert into t values(‘string that is more than 2499 character long in one line so that full coomand beginning from insert becomes more than 2500 characters’);

 

SQL> @c:\1.sql

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

SQL>

Solution


 This is a SQL*Plus limitation.

The sql command line length is limited to 2499 characters, this includes insert etc. commands and not only limited to string that may be more than 2500 character in length.

http://docs.oracle.com/cd/B19306_01/server.102/b14357/apa.htm#i635278%20

 sqlplus limitation

 

 

 

 

 

 

Method 1

To overcome this error SP2-0027: Input is too long (> 2499 characters) – line ignored, the sql command need to be formatted so that any single line does not exceed 2500 characters. So in the command new line characters can be introduced as much as possible to restrict single line length to 2500 characters.

 Method 2

For large strings for which there is no new line up to 2499 characters, divide the string in multiple string parts and concatenate the strings using || so that each line is less than 2499 characters and place the next string in new line after concatenate (||) symbol.

insert into t values(‘string of length -2499||

‘string of length 2499’ ||

‘….’);

  Method 3

Also the SQL Developer tool can be used that does not have such limitation.

 

 sqldeveloper

 

 

 

 

Conclusion


 To overcome the issue of SP2-0027: Input is too long (> 2499 characters) – line ignored the following can be done:

  1. Format the sql command so that it contains less than 2500 characters in single line
  2. For large string use concatenate operator after dividing the string in multiple segments.
  3. Use SQL Developer tool.

Leave a reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

*