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

neves_10335 · Answer

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.

user_1610853 · Answer

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 Answers