6 Replies Latest reply: Apr 25, 2013 1:14 AM by Marco Gralike RSS

    String literal too long for CLOB

    937454
      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
          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
            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
              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
                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
                  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
                    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