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

User · Answer

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"

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

Examples related to oracle