sql - How to insert CLOB more then 1 Mb (1kk characters) in Oracle by script -

how insert clob more 1 mb (1kk characters) in oracle script exmpl. using pl slq, maybe append parts less 32767 bytes(chars). bypass problem: "pls-00172: string literal long".

here target table:

create table qon (x clob);  

here code throws error:

declare      l_clob clob := '32769 chars+ ';  begin      in 1..2      loop          insert qon (x) values (empty_clob()) --insert "empty clob" (not insert null)          returning x l_clob; -- can append content clob (create 400,000 bytes clob)          j in 1..3 loop              dbms_lob.append(l_clob, rpad ('',4000,'')); --dbms_lob.append(l_clob, 'string chunk inserted (maximum 4000 characters @ time)');          end loop;       end loop;  end; 

sorry, tomorow correct. idea - somehow insert string more 32767

urls i'm searched:

oralce clob can't insert beyond 4000 character?

how query clob column in oracle

http://www.oradev.com/dbms_lob.jsp

how write oracle insert script 1 field clob? http://www.techonthenet.com/oracle/functions/rpad.php

how insert/update larger size of data in oracle tables?

https://www.aquaclusters.com/app/home/project/public/aquadatastudio/issue/8179

https://community.oracle.com/thread/2545044

thanks

you seem understand problem, i'm not sure why you're having difficulty implementation.

here version of table:

create table qon    ( id number      , txt clob      , len number ) / 

and here version of code:

declare     l_clob clob;     --  4000 characters limit rpad in sql      l_str  varchar2 (32767);     n number; begin     l_str := rpad ('string chunk inserted (maximum 32767) characters @ time',32767,'+');     dbms_lob.createtemporary (l_clob,true );     << recordz >>     in 1..2 loop          << appendz >>         j in 1..10 loop              dbms_lob.append (l_clob, l_str);         end loop appendz;          insert qon               values (i, l_clob, dbms_lob.getlength(l_clob))             returning qon.len n;         dbms_output.put_line('#'||i||' length of clob = '||n);      end loop recordz;       dbms_lob.freetemporary (l_clob); end; / 

it's output is:

 ...  21* end; #1 length of clob = 327670 #2 length of clob = 655340  pl/sql procedure completed.  sql>