DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

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

Hybrid View

  1. #1
    Join Date
    Jul 2001
    Posts
    3
    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

  2. #2
    Join Date
    Mar 2001
    Posts
    314
    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


  3. #3
    Join Date
    Jul 2001
    Posts
    3
    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

  4. #4
    Join Date
    Mar 2001
    Posts
    314


    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

  5. #5
    Join Date
    Jul 2001
    Posts
    3
    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
  •  


Click Here to Expand Forum to Full Width