• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8313
  • Last Modified:

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.
0
hc2342uhxx3vw36x96hq
Asked:
hc2342uhxx3vw36x96hq
1 Solution
 
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now