oracle单行超2499,SP2-0027: Input is too long(> 2499 characters)错误变通解决方法

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.

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.

Conclusion

To overcome the issue of SP2-0027: Input is too

long (> 2499 characters) – line ignored the following can be

done:

Format the sql command so that it contains less than 2500

characters in single line

For large string use concatenate operator after dividing the

string in multiple segments.

Use SQL Developer tool.