-
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#
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|