How to write oracle insert script with one field as CLOB?

Question

I want to create an insert script which will be used only to insert one record into one table.

It has 5 columns and one of them is of type CLOB.

Whenever I try, it says can not insert string is so long . larger than 4000.

I need an insert statement with clob as one field.

INSERT INTO tbltablename 
            (id, 
             NAME, 
             description, 
             accountnumber, 
             fathername) 
VALUES      (1, 
             N'Name', 
             clob'some very long string here, greater than 4000 characters', 
             23, 
             'John') ;

score 24 · Answer 1 · edited Jul 29 '15 at 07:00

24

Keep in mind that SQL strings can not be larger than 4000 bytes, while Pl/SQL can have strings as large as 32767 bytes. see below for an example of inserting a large string via an anonymous block which I believe will do everything you need it to do.

note I changed the varchar2(32000) to CLOB

set serveroutput ON 
CREATE TABLE testclob 
  ( 
     id NUMBER, 
     c  CLOB, 
     d  VARCHAR2(4000) 
  ); 

DECLARE 
    reallybigtextstring CLOB := '123'; 
    i                   INT; 
BEGIN 
    WHILE Length(reallybigtextstring) <= 60000 LOOP 
        reallybigtextstring := reallybigtextstring 
                               || '000000000000000000000000000000000'; 
    END LOOP; 

    INSERT INTO testclob 
                (id, 
                 c, 
                 d) 
    VALUES     (0, 
                reallybigtextstring, 
                'done'); 

    dbms_output.Put_line('I have finished inputting your clob: ' 
                         || Length(reallybigtextstring)); 
END; 

/ 
SELECT * 
FROM   testclob; 


 "I have finished inputting your clob: 60030"

edited Jul 29 '15 at 07:00

Srini V

11,045
14
66
89

answered Oct 08 '10 at 13:20

Harrison

8,970
1
32
28

3

That's a good answer for CLOBs up to 32'000 characters. For bigger CLOBs, you need to create a temporary CLOB (see DBMS_LOB package), append data to it in chunks of 32'000 and the insert that temporary CLOB into the table. – Codo Oct 08 '10 at 13:27
@codo, that is a really good point, but in this example dbms_lob was unnecessary - at least in 11r1 (and I should not have set the ReallyBigTextString as a VARCHAR2(32000) -- I will amend the above example. – Harrison Oct 08 '10 at 13:33
You're partially right. The use of DBMS_LOB is probabably not needed. But if you're not using an artificial string - as you do by repeating the same substring again and again - then you're limited by the size of the declare/begin/end block. At a certain size (about 100K), you get an error message. So you have to split your long string into chunks and split it over several declare/begin/end blocks. At that's rather tricky. – Codo Oct 08 '10 at 13:58
1

Also keep in mind that SQL*Plus only supports 2499 characters on a line. – Jon Heller Oct 08 '10 at 23:16
@Codo, in Oracle 10g a feature called "SQL semantics for LOB" was introduced. Thus for certain operations like concatenation package `DBMS_LOB` is not required anymore. – Wernfried Domscheit May 11 '18 at 05:36

neves · Answer 2 · 2022-03-09T22:10:19.493

I solved my problem with a solution that is simpler than the most voted answer.

You must divide your big clob string into multiple strings, each one with less than 4000 chars, convert each one using the to_clob method, and concatenate them with the || operator.

Here is an example of the final insert statement:

INSERT INTO tbltablename 
            (id, 
             name, 
             big_clob_description) 
VALUES      (1, 
             N'A Name', 
             to_clob('string with less than 4000 chars') 
             || to_clob('rest of string here, with less than 4000 chars') 
) ;

My insert code was generated by a script, and it wasn't difficult to break the strings.

score 2 · Answer 3 · answered Aug 16 '21 at 20:41

You can use the to_clob function too.

INSERT INTO tbltablename 
            (id, 
             NAME, 
             description, 
             accountnumber, 
             fathername) 
VALUES      (1, 
             N'Name', 
             to_clob('clob''some very long string here, greater than 4000 characters'), 
             23, 
             'John') ;

You can find more information here: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions182.htm.

Regards.

How to write oracle insert script with one field as CLOB?

3 Answers3

Linked