Insert a long value ORA-01704: string literal too long

Insert a long value ORA-01704: string literal too long

Post by kir.. » Sun, 31 Dec 1899 09:00:00



hi everyone,
i've defined a table with a LONG column to store text containing
more than 4000 car.
The problem is that when i do an insert like :

insert into my_table (long_value) values ('patati.... patata')

i have the oracle error : ORA-01704: string literal too long...

if 'patati....patata' has less than 4000 car. : it's ok.

What's the problem ? is it that i use quotes ?
it seems that 'patati....patata' is considered like a VARCHAR2 data.

thank u for help

Cyril

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Insert a long value ORA-01704: string literal too long

Post by spaced.. » Sun, 31 Dec 1899 09:00:00


I've had the same problem with any inserts over
2000 chars.  ora-01704 errors all over the
place.  the strange thing is this: it happens in
a web-based db query program, but also in SQL
worksheet.  hmm...i never had problems like this
with microsoft sql server...
chris



> hi everyone,
> i've defined a table with a LONG column to

store text containing
Quote:> more than 4000 car.
> The problem is that when i do an insert like :

> insert into my_table (long_value) values

('patati.... patata')
Quote:

> i have the oracle error : ORA-01704: string
literal too long...

> if 'patati....patata' has less than 4000 car. :
it's ok.

> What's the problem ? is it that i use quotes ?
> it seems that 'patati....patata' is considered

like a VARCHAR2 data.

Quote:

> thank u for help

> Cyril

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Insert a long value ORA-01704: string literal too long

Post by Thomas Kyt » Sun, 31 Dec 1899 09:00:00



(if that email address didn't require changing)


>I've had the same problem with any inserts over
>2000 chars.  ora-01704 errors all over the
>place.  the strange thing is this: it happens in
>a web-based db query program, but also in SQL
>worksheet.  hmm...i never had problems like this
>with microsoft sql server...
>chris

You need to use bind variables for character string constants over 2000
character in v7.x and over 4000 characters in 8.x  (

See http://osi.oracle.com/~tkyte/Misc/LargeStrings.html for the SQL you can use
if you must use a character string constant (as opposed to a much more efficient
bind variable) of upto 32k in size.

You won't have to double up the quotes with bind variables either -- they are
really the way to go (and don't have 2000/4000 byte limits either)



>> hi everyone,
>> i've defined a table with a LONG column to
>store text containing
>> more than 4000 car.
>> The problem is that when i do an insert like :

>> insert into my_table (long_value) values
>('patati.... patata')

>> i have the oracle error : ORA-01704: string
>literal too long...

>> if 'patati....patata' has less than 4000 car. :
>it's ok.

>> What's the problem ? is it that i use quotes ?
>> it seems that 'patati....patata' is considered
>like a VARCHAR2 data.

>> thank u for help

>> Cyril

>> Sent via Deja.com http://www.deja.com/
>> Before you buy.

>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st


Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation