Press "Enter" to skip to content

How to Insert a Long String into CLOB Data Type in Oracle

A simple example is given below to insert a long string into CLOB data type in Oracle. You can not insert data directly into CLOB data type, you have to use Empty_Clob() function with returning clause in insert statement then use dbms_lob.write procedure to write the data. Because if you will try to insert directly a long string into CLOB column then you will get ORA-01704: string literal too long error while inserting the data.

Here is the PL SQL block example to insert a long string into a table having CLOB column.

[code type=”SQL”] DECLARE
v_clob   CLOB;
BEGIN
INSERT INTO A_CLOB_TABLE (srlno, c_data)
VALUES (123, EMPTY_CLOB ())
RETURNING c_data
INTO v_clob;

DBMS_LOB.write (v_clob,
LENGTH (‘provide some long string here’),
1,
‘provide some long string here’);
COMMIT;
END;[/code]

Insert long string into clob data type in Oracle