This discussion is archived
6 Replies Latest reply: Apr 24, 2013 11:14 PM by Marco Gralike RSS

String literal too long for CLOB

937454 Newbie
Currently Being Moderated
Hi,
My database version is:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE     11.2.0.1.0     Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

When I run this piece of code, I get an error.
DECLARE
insCtx DBMS_XMLStore.ctxType;
rows NUMBER;
xmlDoc CLOB := 'xml string is approx 4600 lines long in TOAD...........................';
BEGIN
insCtx := DBMS_XMLStore.newContext ('xmlTempTbl'); -- get the context
rows := DBMS_XMLStore.insertXML (insCtx, xmlDoc); -- insert the doc
DBMS_XMLStore.closeContext (insCtx); -- close the handle
END;

ERROR:
ORA-06550: line 4, column 19:
PLS-00172: string literal too long

From my understanding a CLOB can take maximum 2GB size string. And from above, my xml string is not greater than 2gb (I cannot post the xml string data, refers to business). Then why am I getting the string literal too long error.
For the above, I have to breakdown the xml into several parts and run the PLSQL, then it does not give any error.
Please advice.

Edited by: 934451 on Nov 8, 2012 7:06 AM

Edited by: 934451 on Nov 8, 2012 7:07 AM
  • 1. Re: String literal too long for CLOB
    odie_63 Guru
    Currently Being Moderated
    From my understanding a CLOB can take maximum 2GB size string.
    Not exactly :
    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/datatypes.htm#CHDDCGEE

    but that's not the actual problem.

    The error is about the "string literal" size, there's a limitation too :
    http://docs.oracle.com/cd/E11882_01/server.112/e17766/pcmus.htm#sthref18132
    PLS-00172: string literal too long
        Cause: The string literal was longer than 32767 bytes.
        Action: Use a string literal of at most 32767 bytes.
    For the above, I have to breakdown the xml into several parts and run the PLSQL, then it does not give any error.
    Is that a real production case or are you just testing?
    In practice, one should first store the file in a CLOB (or XMLType) column/variable and then reference the content from there.
  • 2. Re: String literal too long for CLOB
    937454 Newbie
    Currently Being Moderated
    It's not a production issue. I just had a requirement to load the xml into the xmlTempTbl, and I came across this problem.
    Its still confusing.
    A CLOB can take a max size of 128TB.
    And at the same time it cannot exceed 32767 bytes.
    Then I should declare
    xmlDoc VARCHAR2(32767) := 'xml string';
    What benefir am I getting by declaring it as
    xmlDoc CLOB := 'xml string';

    Please advice. I may be seeing it from a wrong direction I guess.

    Edited by: 934451 on Nov 8, 2012 7:31 AM
  • 3. Re: String literal too long for CLOB
    odie_63 Guru
    Currently Being Moderated
    A CLOB can take a max size of 128TB.
    And at the same time it cannot exceed 32767 bytes.
    You still don't get it.

    The issue is the limitation in the size we can pass in a single constant string (aka "string literal"), it doesn't matter if the target variable is of VARCHAR2 or CLOB.
    Then I should declare
    xmlDoc VARCHAR2(32767) := 'xml string';
    If you do that, now the target variable becomes a limiting factor as well, in addition to the size of the string literal.
    What benefir am I getting by declaring it as
    xmlDoc CLOB := 'xml string';
    Ok, say your XML string is 70.000 bytes long.
    Obviously you can't assign it to a VARCHAR2 since the upper limit of that datatype is 32767 (in PL/SQL).
    So you have to use CLOB.
    However, you can't assign the whole string directly since it breaks the 32k limitation for a string literal.
    As you've already found yourself, the only solution is then to split it in 3 chunks and use the concatenation operator :
    xmlDoc CLOB := 'xml chunk #1' || 'xml chunk #2' || 'xml chunk #3';
    or use an external source.
  • 4. Re: String literal too long for CLOB
    937454 Newbie
    Currently Being Moderated
    I get it this time. Thank you!
    Just for testing. This time I used the code you suggested
    xmlDoc CLOB := 'xml chunk #1' || 'xml chunk #2' || 'xml chunk #3';
    But I get a different error:
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at line 4
    But it makes sense, why I am getting this error, though I cannot exactly put it in correct words.

    Edited by: 934451 on Nov 8, 2012 8:59 AM

    Edited by: 934451 on Nov 8, 2012 9:04 AM
  • 5. Re: String literal too long for CLOB
    1002022 Newbie
    Currently Being Moderated
    I had the same problem.

    And solved it :D
        strSqlUpdate = "InClob := '" & replace(Mid(inhoud,(teller*3000)+1, 3000),"'","''") & "'; "
        if cdbl(aantal)>1 then
            For teller = 1 to aantal 
                strSqlUpdate = strSqlUpdate &  "InClob := InClob || '" & replace(Mid(inhoud,(teller*3000)+1, 3000),"'","''") &"'; " & vbNewLine
            next
        end if
    
        str_SQL = "DECLARE InClob CLOB; " 
        str_SQL = str_SQL & "BEGIN " 
        str_SQL = str_SQL & strSqlUpdate & vbNewLine
        str_SQL = str_SQL & "update "& tabel &" " 
         str_SQL = str_SQL & "set "& kolom &" = InClob "
         str_SQL = str_SQL & "where "& str_sql_where & "; "
        str_SQL = str_SQL & "COMMIT; " 
        str_SQL = str_SQL & "END; " 
        str_SQL = str_SQL & " " 
  • 6. Re: String literal too long for CLOB
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    Just to be sure, people are aware. If you use *||* then changes are that in an intermediate result, you will convert (implicit conversion) in between results to VARCHAR2(), and therefore hitting this buffer/limit.

    Edited by: Marco Gralike on Apr 25, 2013 8:14 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points