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

[irosa]

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

[amar]

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

[irosa]

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

[amar]

:)

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

[irosa]

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:

<cfquery name="upd" datasource="#request.site.MainDSN#">
UPDATE MKT_Page_Links
SET LongDescription = <CFQUERYPARAM VALUE="#DataString#" CFSQLTYPE="CF_SQL_CLOB">
WHERE MenuIDNO = #form.ID#
</cfquery>