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

How can I avoid this error?  Please assume that I am obliged to use insert statements.
LVL 1
hc2342uhxx3vw36x96hqAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Om PrakashCommented:
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
0
hc2342uhxx3vw36x96hqAuthor Commented:
Sorry, it doesn't work.

Any other clues?!
0
hc2342uhxx3vw36x96hqAuthor Commented:
I use Oracle 10G R2 on Windows Vista.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Shaju KumbalathDeputy General Manager - ITCommented:
have u tried setting line size ?
eg:
set line 5000
 
0
hc2342uhxx3vw36x96hqAuthor Commented:
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?
0
hc2342uhxx3vw36x96hqAuthor Commented:
Any help?
0
sdstuberCommented:
what are you trying to do that generates the long strings?

0
hc2342uhxx3vw36x96hqAuthor Commented:
i cannot answer the site is blocked
0
hc2342uhxx3vw36x96hqAuthor Commented:
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)
);
0
hc2342uhxx3vw36x96hqAuthor Commented:
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.
0
hc2342uhxx3vw36x96hqAuthor Commented:

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) 
); 

Open in new window

0
hc2342uhxx3vw36x96hqAuthor Commented:
The record with the longest occurrence of C13 has a C13 field of length over 3000 characters.
0
sdstuberCommented:
change how you do your "exports"

so your your long strings are broken into pieces,  something like this...

if you can't change what you are calling an "export", then you will probably need to do some post-processing with a text editor, sed, awk or other tool


Insert into SDS.SAMPLE_TABLE
   (C01, C02, C03, C04, C05, C06, C07, C08, C09, C10, C11, C12, C13, C14, C15, C16)
 Values
   ('12345', 1234567890, 12345, 12345, '12345678901234567890', 
    '12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890', '12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890', 12345, 1234567890, '12345678901234567890123456789012345678901234567890123456789012345678901234567890', 
    '12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890', '12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890', '12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890' ||
	'1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890' || '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890' || 
	'12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890' || 
	'12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890' ||
'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890', 'N', 'A', 
    'B');

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
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
0
hc2342uhxx3vw36x96hqAuthor Commented:
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.
0
sdstuberCommented:
how are you getting the insert statements?  modify that process
0
hc2342uhxx3vw36x96hqAuthor Commented:
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.

0
sdstuberCommented:
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.

0
hc2342uhxx3vw36x96hqAuthor Commented:
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...
0
sdstuberCommented:
yes,  after you export your sql statements, you modify them with the tool of your choice so that sql* plus can run them
0
hc2342uhxx3vw36x96hqAuthor Commented:
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...
0
sdstuberCommented:
what version of sqlplus were you using?  I successfully inserted 4000 characters by breaking it into multiple lines


I tested it in http:#32642559
0
hc2342uhxx3vw36x96hqAuthor Commented:

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> 

Open in new window

0
sdstuberCommented:
I used 11.2 client
0
hc2342uhxx3vw36x96hqAuthor Commented:
I can use only 10G R2.
0
hc2342uhxx3vw36x96hqAuthor Commented:
10KU
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.