ORA-01704: string literal too long

[borisch]

I am trying to run SQL statement to update one field with a datatype "clob"
and SQL give me an error "ORA-01704: string literal too long ".
The len of the string I am trying to save is 8336 chr. It could be longer or
shorter depending on a webpage.
If I take only 4000 chr left(XHTML,4000) then everything is fine. I thought
that CLOB take 4GB of text. What am I doing wrong? Please help.

I have a table like this:
CREATE TABLE SMKT.INT_SESSIONLOG
(
LOGID VARCHAR2(50) NOT NULL,
SESSIONID VARCHAR2(50) NOT NULL,
DATECREATED VARCHAR2(50) NOT NULL,
PAGEXML CLOB DEFAULT empty_clob() NULL,
XHTML CLOB DEFAULT empty_clob() NULL,
USERID VARCHAR2(50) NOT NULL,
)

and

update statement like this:

Update INT_SESSIONLOG SET XHTML = '" & mXHTML.xml "' WHERE LogID = '" &
msLogID & "'"

I am trying to run this SQL from VB. For now sql statement inside VB code.
I will create a stored procedure and put it inside a package but not today.
Most of the time data will be more then 32 K.

Thanks a lot.
Boris

 

[jee]

Here is the text from the Oracle Error Messages manual:

ORA-01704 string literal too long

Cause: The string literal is longer than 4000 characters.

Action: Use a string literal of at most 4000 characters. Longer values may only be entered using bind variables.


The problem is not the CLOB, it is the literal in the SQL statement.

 

[borisch]

yes, if I make a string 4000 chr it work but how can i saqve 8000 0r 10000 or even more? I need to use clob as a field datatype. I can create a package and pass string of 8000 chr into package but looks like I will have same error that string is too long. How can I insert/update/select value from clob field. Value will be more then 4000 chr.

 

[spenglerr]

Hi,

you will have to use the package dbms_lob which is delivered with your database to handle lob-data. It includes methods like read, write, writeappend, and so on.

Actually the limit of character data is always 32kB. So you will have to write the data in chunks to the CLOB using a string buffer of 32k.

If you are working with SQL*Plus it is never wrong to tell SQL*Plus to use the full range of 32k by typing:

SET MAXDATA 32767

that's it so far, for dbms_lob look in your Oracle-Doku in the book Supplied PL/SQL packages.