CODEWITHSUNDEEP
sqloracle-databaseclobora-01704
Rami Nassar
Rami Nassar

Reputation: 403

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

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.

Upvotes: 0

Views: 724

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31666

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;
/

Upvotes: 0

Related Questions