ORA-01704: String Literal Too Long - Help!!

I had a field that was varchar2(4000) and changed it to CLOB to allow more than 4000 characters. When I try to do and update to the table, I still get this error when updating this field with more than 4000 characters.

Does the CLOB field support using the update statement? This is being done using straight SQL through a ColdFusion web application. Are there any attributes that need to be set for field length when using CLOB? Any help would be greatly appreciated.

Thanks,
Issac

CLOB fields do support the SQL update statement (the statement
update xyztable set clob_col='this is a modified clob col val';

will work as expected.

And you do not need to set any length attributes for CLOB fields either :)

With PL/SQL you can populate a CLOB col with a max value of 32767 (using the max length supported for char/varchar2 types in pl/sql). If you need to populate the CLOB col with larger values you will have to use the DBMS_LOB package functions WRITE or WRITEAPPEND.

-amar

Thanks for the response.

Then why do I get the ORA-01704: string literal too long when doing an SQL update statement in SQL*Plus and also executing an SQL query in ColdFusion.

Here is the sql statement being executed:
update mkt_page_links
set longdescription = ' {some html text > 4000 char} '
where menuidno = 310;

- issac

:)

You get the error because you are executing a sql statement.
In SQL the max of varchar2 is 4000 bytes and hence the error. Modify your code snippet to something like the following and it should work. (it becomes a pl/sql program).

declare
myClobVar varchar2(32767) := 'string >4000 and <32767 bytes';
begin
update tableWithClob set clobcol = myClobVar;
end;

-amar

Thanks for the info. The only problem is that I am using ColdFusion to execute the query. I figured it out though. CF has a parameter that converts the variable to CLOB datatype.

Example query in CF:


UPDATE MKT_Page_Links
SET LongDescription =
WHERE MenuIDNO = #form.ID#