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

Question

How to insert CLOB more then 1 Mb (1kk characters) in Oracle by script For exmpl. using pl slq, maybe append some parts less then 32767 bytes(chars). To bypass the problem: "PLS-00172: string literal too long".

Here is my target table:

CREATE TABLE qon (x clob); 

Here is the code which throws the error:

DECLARE 
    l_clob clob := '32769 chars+ '; 
BEGIN 
    FOR i IN 1..2 
    LOOP 
        INSERT INTO qon (x) VALUES (empty_clob()) --Insert an "empty clob" (not insert null) 
        RETURNING x INTO l_clob; -- Now we can append content to clob (create a 400,000 bytes clob) 
        FOR j IN 1..3 LOOP 
            dbms_lob.append(l_clob, rpad ('',4000,'')); --dbms_lob.append(l_clob, 'string chunk to be inserted (maximum 4000 characters at a time)'); 
        END LOOP; 
     END LOOP; 
END;

Sorry, tomorow will correct. This idea - somehow insert string more then 32767

Urls I'm searched:

Oralce CLOB can't insert beyond 4000 character?

How to query a CLOB column in Oracle

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

How to write oracle insert script with one field as CLOB? http://www.techonthenet.com/oracle/functions/rpad.php

How to insert/update larger size of data in the Oracle tables?

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

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

Thanks

Answer 1

After debugging working version to manipulate CLOB value using UTF-8 encoding:

drop table demo;
drop sequence clob_seq;

create table demo
( id           int primary key,
  theclob      clob
)
/

create or replace directory MY_FILES as 'C:\hs';
create sequence clob_seq;

create or replace
procedure dbst_load_a_file( p_dir_name in varchar2,p_file_name in varchar2 )
    as
        l_clob    clob;
        l_bfile   bfile;
        dst_offset  number := 1 ;
        src_offset  number := 1 ;
        lang_ctx    number := DBMS_LOB.DEFAULT_LANG_CTX;
        warning     number;
    begin
        insert into demo values ( clob_seq.nextval, empty_clob() )returning theclob into l_clob;
        l_bfile := bfilename( p_dir_name, p_file_name );
        dbms_lob.fileopen( l_bfile );
        dbms_lob.createtemporary(l_clob, true); -- attention: needed
        dbms_lob.loadclobfromfile( 
          DEST_LOB     => l_clob
        , SRC_BFILE    => l_bfile
        , AMOUNT       => dbms_lob.getlength( l_bfile )
        , DEST_OFFSET  => dst_offset
        , SRC_OFFSET   => src_offset
        , BFILE_CSID   => DBMS_LOB.DEFAULT_CSID
        , LANG_CONTEXT => lang_ctx
        , WARNING      => warning);
        dbms_lob.fileclose( l_bfile );
    end;

exec dbst_load_a_file( 'MY_FILES', 'myBigText.txt' );

Answer 2

You seem to understand the problem, I'm not sure why you're having difficulty with the implementation.

Here is my version of your table:

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

And here is my version of your code:

declare
    l_clob clob;
    --  4000 characters is the limit for RPAD in SQL 
    l_str  varchar2 (32767);
    n number;
begin
    l_str := rpad ('string chunk to be inserted (maximum 32767) characters at a time',32767,'+');
    dbms_lob.createtemporary (l_clob,true );
    << recordz >>
    for i in 1..2 loop 
        << appendz >>
        for j in 1..10 loop 
            dbms_lob.append (l_clob, l_str);
        end loop appendz; 
        insert into qon  
            values (i, l_clob, dbms_lob.getlength(l_clob))
            returning qon.len into 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 successfully completed.

SQL> 

Answer 3

This code I suppose could help to insert large text into a BLOB(CLOB) column

SQL> drop table demo;
Table dropped.

SQL> drop sequence blob_seq;
Sequence dropped.

SQL> create table demo
  2  ( id           int primary key,
  3    theBlob      blob
  4  )
  5  /
Table created.

SQL> create or replace directory my_files as 'C:\hs';
Directory created.

SQL> create sequence blob_seq;
Sequence created.

SQL> create or replace
  2  procedure dbst_load_a_file( p_dir_name in varchar2,p_file_name in varchar2 )
  3  as
  4      l_blob    blob;
  5      l_bfile   bfile;
  6  begin
  7      insert into demo values ( blob_seq.nextval, empty_blob() )returning theBlob into l_Blob;
  8      l_bfile := bfilename( p_dir_name, p_file_name );
  9      dbms_lob.fileopen( l_bfile );
 10      dbms_lob.loadfromfile( l_blob, l_bfile,dbms_lob.getlength( l_bfile ) );
 11      dbms_lob.fileclose( l_bfile );
 12  end;
 13  /
Procedure created.

SQL> exec dbst_load_a_file( 'MY_FILES', 'my.jpg' );
PL/SQL procedure successfully completed.

SQL> select dbms_lob.getlength(theblob) from demo;
DBMS_LOB.GETLENGTH(THEBLOB)
---------------------------
                    1964427