SQL Plus error sp2 0027 input is too long (greather than 2499 characters)

you have a line longer than 2499 characters (do you have trimspool on?  
else spool files have a fixed length line, that of set linesize).  
that is a fixed limit in sqlplus.
in the spool file, set echo off does go for it.  put this in a script:

spool foo
set echo on
select 'hello world 1' from dual;
set echo off
select 'hello world 2' from dual;
spool off

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:30802454515375

Sorry, it doesn't work.

Any other clues?!

I use Oracle 10G R2 on Windows Vista.

have u tried setting line size ?
eg:
set line 5000
 

2500 is a STANDARD limit of SQL*Plus: you cannot override it by setting LINESIZE parameter.

But if you EXPORT AS INSERT STATEMENTS, you obtain lines overriding that limit of 2500 characters.

This means we need a WORKAROUND, in example a function to compress a VARCHAR2, or a sequence of INSERT / UPDATE / INSERT / UPDATE statements generated AUTOMATICALLY, and so on.

Any help?

Any help?

what are you trying to do that generates the long strings?

i cannot answer the site is blocked

CREATE TABLE SAMPLE_TABLE
(
   C01   VARCHAR2 (5 BYTE) NOT NULL,
   C02   NUMBER (10) NOT NULL,
   C03   NUMBER (5) NOT NULL,
   C04   NUMBER (5) NOT NULL,
   C05   VARCHAR2 (20 BYTE) NOT NULL,
   c06   VARCHAR2 (200 BYTE) NOT NULL,
   c07   VARCHAR2 (200 BYTE) NOT NULL,
   c08   NUMBER (5) NOT NULL,
   c09   NUMBER (10) NOT NULL,
   c10   VARCHAR2 (80 BYTE),
   c11   VARCHAR2 (200 BYTE),
   c12   VARCHAR2 (200 BYTE),
   c13   VARCHAR2 (4000 BYTE),
   c14   VARCHAR2 (1 BYTE) DEFAULT 'N' NOT NULL,
   c15   CHAR (1 BYTE),
   c16   CHAR (1 BYTE)
);

I have to export an Oracle table as INSERT STATEMENTS.
But the INSERT STATEMENTS so generated, override 2500 characters.
I am obliged to execute them in SQL Plus, so I receive an error message.
This is my Oracle table:
CREATE TABLE SAMPLE_TABLE  (     C01   VARCHAR2 (5 BYTE) NOT NULL,     C02   NUMBER (10) NOT NULL,     C03   NUMBER (5) NOT NULL,     C04   NUMBER (5) NOT NULL,     C05   VARCHAR2 (20 BYTE) NOT NULL,     c06   VARCHAR2 (200 BYTE) NOT NULL,     c07   VARCHAR2 (200 BYTE) NOT NULL,     c08   NUMBER (5) NOT NULL,     c09   NUMBER (10) NOT NULL,     c10   VARCHAR2 (80 BYTE),     c11   VARCHAR2 (200 BYTE),     c12   VARCHAR2 (200 BYTE),     c13   VARCHAR2 (4000 BYTE),     c14   VARCHAR2 (1 BYTE) DEFAULT 'N' NOT NULL,     c15   CHAR (1 BYTE),     c16   CHAR (1 BYTE)  );  

ASSUMPTIONS:
a) I am OBLIGED to export table data as INSERT STATEMENTS; I am allowed to use UPDATE statements, in order to avoid the SQL*Plus error "sp2-0027 input is too long(>2499 characters)";
b) I am OBLIGED to use SQL*Plus to execute the script so generated.
c) Please assume that every record can contain special characters: CHR(10), CHR(13), and so on;
d) I CAN'T use SQL Loader;
e) I CAN'T export and then import the table: I can only add the "delta" using INSERT / UPDATE statements through SQL Plus.

This is my Oracle table:

CREATE TABLE SAMPLE_TABLE 
( 
   C01   VARCHAR2 (5 BYTE) NOT NULL, 
   C02   NUMBER (10) NOT NULL, 
   C03   NUMBER (5) NOT NULL, 
   C04   NUMBER (5) NOT NULL, 
   C05   VARCHAR2 (20 BYTE) NOT NULL, 
   c06   VARCHAR2 (200 BYTE) NOT NULL, 
   c07   VARCHAR2 (200 BYTE) NOT NULL, 
   c08   NUMBER (5) NOT NULL, 
   c09   NUMBER (10) NOT NULL, 
   c10   VARCHAR2 (80 BYTE), 
   c11   VARCHAR2 (200 BYTE), 
   c12   VARCHAR2 (200 BYTE), 
   c13   VARCHAR2 (4000 BYTE), 
   c14   VARCHAR2 (1 BYTE) DEFAULT 'N' NOT NULL, 
   c15   CHAR (1 BYTE), 
   c16   CHAR (1 BYTE) 
); 

Select allOpen in new window

The record with the longest occurrence of C13 has a C13 field of length over 3000 characters.

alternatively, write your own insert statement generator.
something like this....

select 'INSERT INTO sample_table values (''' || c01 || ''',' || c02 || ',' || c03 ||',' || c04

etc

and when you get to the big string, don't append it directly,  append it as a series of SUBSTR from 1-1000, 1001-2000, 2001-3000 and 3001-4000

OK, but is there any way to AUTOMATE this process?

I have a lot of records to process in this way.

If you suppose to have 5,000 records with the c13 field overbound, you'll realize that it cannot be done manually.... we need to automate the process.

The reason of this question is at last the following: automate the extraction of Oracle data from a table, producing INSERT STATEMENTS compliant with the 2499 characters limit in SQL*Plus.

The team that will execute the scripts in production environment, processes only in SQL*Plus, and rejects all the technology solutions not SQL*Plus compliant.

how are you getting the insert statements?  modify that process

I am getting the INSERT STATEMENTS using TOAD, using the opzione "Export as Insert Statements".

I can create VIEWS or other TEMPORARY TABLES, if necessary.

Toad's export has a one column per line option, after you export, use some text processing tool, perl,awk,sed, etc to parse the file and break long lines into multiple lines.

But I have to execute the insert statements in SQL*Plus, not in TOAD, so I overpass the limit of 2499 characters for every line...

yes,  after you export your sql statements, you modify them with the tool of your choice so that sql* plus can run them

Your suggestion does not work, because SQL Plus counts the characters even if you split the line into more lines...  I thought an INSERT / UPDATE solution, but I am not able to implement it...

what version of sqlplus were you using?  I successfully inserted 4000 characters by breaking it into multiple lines


I tested it in http:#32642559

SQL*Plus: Release 10.2.0.3.0 - Production on Mon May 10 10:15:06 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production
With the Partitioning, OLAP and Data Mining Scoring Engine options

SQL> 

Select allOpen in new window

I used 11.2 client

I can use only 10G R2.

10KU