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') ;

Answer 1

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"

Answer 2

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.

Answer 3

What solved my problem: you divide in multiple strings with less than 4000 chars and concatenate them with || and use the to_clob method.

I was programatically generating the insert code and this was a clearer solution:

INSERT INTO tbltablename 
            (id, 
             NAME, 
             description, 
             accountnumber, 
             fathername) 
VALUES      (1, 
             N'Name', 
             to_clob('string with less than 4000 chars') 
             || to_clob('rest of string here, with less than 4000 chars') ,
             23, 
             'John') ;