Is there any way using generic sql to insert a string longer than 4000 chars into a CLOB

Question

Is there any way using generic sql to insert a string longer than 4000 chars into a CLOB

New Column Added:

ALTER TABLE MESSAGES ADD (MESSAGE_CLOB CLOB);

INSERT INTO TF_STP_MESSAGES
           (ID,
            MESSAGE_CLOB)
    VALUES (MSG_SEQ.NEXTVAL,
            TO_CLOB ('STRING WITH 10000 CHARACHTER'));

Oracle returns the following message:

PL/SQL: ORA-01704: string literal too long

I have to insert it by SQL

Please advise.

Answer 1

You need to use an anonymous block (or a procedure) to run the insert statement by declaring as a CLOB variable and using it in insert.

DECLARE

v_message_clob CLOB := 'STRING WITH 10000 CHARACTERS';

BEGIN 
INSERT INTO TF_STP_MESSAGES
           (ID,
            MESSAGE_CLOB)
    VALUES (MSG_SEQ.NEXTVAL,
            v_message_clob);
END;
/