7 Replies Latest reply: Aug 23, 2012 8:32 AM by 957544 RSS

    ORA-01704: string literal too long

    568998
      Is there any way using generic sql to insert a string longer than 4000 chars into a CLOB? I can't use PLSQL it has to be generic sql using a quoted string. But I can do any specific Oracle code before loading the file containing the sql.

      Example,
      <Oracle specific code>

      insert into test (col1) values ('<string longer than 4000 chars');
        • 1. Re: ORA-01704: string literal too long
          gdarling - oracle
          You can't do it with a literal as part of the sql statement.

          You'll need to use a bind variable instead, and send the data in chunks, the same as you would if it were LONG datatype, and that code is not specific to Oracle.

          Hope it helps,
          Greg
          • 2. Re: ORA-01704: string literal too long
            568998
            I need to do the insert using sql script (no bind parameters). The same sql script is loaded by the app for Oracle and many other databases and the script files have multiple/many sql statements that populate the database. I only encountered the sql literal limit of 4000 chars in the case of Oracle.
            • 3. Re: ORA-01704: string literal too long
              758270
              I'm having the same problem as Farid. Are there any resources online for how to send data in chunks using bind variables and the ODBC C API?
              • 4. Re: ORA-01704: string literal too long
                568998
                If you are using ODBC API you don't need to do anything special for long data just bind parameter as usual and the ODBC driver will update the database whether the data length is 4 chars or 2 GB long (you would need to bind your parameter appropriately for long data, ie SQL_LONGVARCHAR instead of SQL_VARCHAR, etc).

                If you want to retrieve/send data in chunks you would use SQLGetData and SQLPutData. Usually only SQLGetData is used with long data, since you probably have all the long data in one block of memory you don't need to use SQLPutData rather just bind a parameter to the memory location as usual with bind parameters.

                The issue I am having is that my data is static loaded from a file as sql statements so using bind parameters is not an option in this case.
                • 5. Re: ORA-01704: string literal too long
                  758270
                  Farid, thanks very much for your help: it turned out that I was using SQL_VARBINARY instead of SQL_LONGVARCHAR. Switching it did the trick.

                  Best of luck getting your own issue resolved.
                  • 6. Re: ORA-01704: string literal too long
                    568998
                    OK I found a workaround that you can use to do the insert using generic SQL (no PL/SQL is required and it works for all databases).

                    1 Do the insert as an insert with first part is the sql literal up to 4000 chars
                    2 Do the additional parts as an update concatenating the previous parts with the next part where the next part is up to 4000 chars
                    3 Repeat step 2 until all the large sql literal is updated.

                    Example,

                    Insert into
                    test_large_literal (col1, col2)
                    values
                    (<key val>, <first part of large sql literal);

                    update
                    test_large_literal
                    set
                    col2 = col2 || <second part large sql literal>
                    where
                    col1 = <key val>;
                    ...
                    ...
                    update
                    test_large_literal
                    set
                    col2 = col2 || <last part large sql literal>
                    where
                    col1 = <key val>;

                    or if you know it is Oracle you can use
                    insert into .. values (to_clob('...') || to_clob('...')..)

                    Edited by: Farid_Z on Nov 10, 2011 11:04 AM
                    • 7. Re: ORA-01704: string literal too long
                      957544
                      Searching the Web for this issue I found your workaround. Let me propose a far simpler solution with no need for temporary tables. Find the full script example at this post: http://blog.ozzie.eu/2011/11/ora-01704-string-literal-too-long.html