I am trying to insert a large csv into an oracle table and I get a ORA-01704: string literal too long error.

The fields are large character data that I am inserting into CLOBs, but you cannot insert a quoted string larger than 2000 characters.

Anyone have some ideas.

    From the Oracle 8.1.5 server doc:

    ORA-01704 string literal too long

    Cause: The string literal is longer than 4000 characters.

    Action: Use a string literal of at most 4000 characters.
    Longer values may only be entered using bind variables.

    Your insert is passing the PHP string data (interpreted as varchar2) which the RDBMS type-converts on the fly for storage into the CLOB column. Unfortunately there are length limitations on varchar2s. Older versions of the Oracle RDBMS (8.0.x and lower) put the limitation at 2000 characters, while newer versions increase the limit to 4000 characters.

    While you could use bind variables as the doc suggests, I recommend using the BLOB/CLOB functions in DBMS_LOB package to add the data in pieces.

    Let me know if you need additional help.

    -- Michael
    Darkstreak Consulting
    www.darkstreak.com

      Write a Reply...