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

Why didn't they help? You seem to know you need to append in 32KiB chunks. Where is the string coming from? Having a 1MiB literal in a script would be a bit unwieldy anyway. Is it coming from a separate file perhaps? — Alex Poole, Jun 13 '15 at 15:23
I've got an idea to have a 1MiB literal stored in the script. About 50 scripts, each one insert row with one clob value in each to DB. All this for bypass the usual insert clob value, based on the ui. In upper url cases I still got a warning about too long string, i've got little experience in pl sql. Thanks — Artem.Borysov, Jun 13 '15 at 15:35
As of now, it is unclear to me what you're asking. May I suggest you to [edit] your question to provide some more context and to show us what/where is your issue _exactly_. — Sylvain Leroux, Jun 13 '15 at 15:44
You can't have a single literal of more than 32KiB in a (PL/)SQL script. You would have to have thousands of shorter literals, which would be a pain to create and maintain. Why can't you have a text file containing the value and load from that instead? If you can get it onto the server anyway. You could use utl_file, an external table, etc. Your requirement doesn't really seem to make sense at the moment. — Alex Poole, Jun 13 '15 at 16:05
Could you please edit your question to specify, whether you can or cannot upload this 1MB file to DB server? — Martina, Jun 13 '15 at 21:13
CREATE TABLE qon (x clob); 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 — Artem.Borysov, Jun 13 '15 at 23:33
Please put new information, especially code, into the question via the edit button - not as a comment. You can append 32KiB at a time, not just 4KB, as it's a Pl/SQL context; but an individual string literal cannot be more than 32KiB. Can you 1MiB 'string' exist in a separate text file on the server? — Alex Poole, Jun 14 '15 at 07:48
Alex Poole understands closer. Yes, my 1MiB 'string' can exist in a separate text file on the server. This will be the better architecture. Only now understend, that script can't store in itself string with more 32767 characters for example in declared clob. Thats why I get notification "string literal to long". As if I will replace script by APC this row: [ begin l_str := rpad ('insert here more then 32767 characters',32767,'+'); ] — Artem.Borysov, Jun 14 '15 at 22:44
I suppose that something like "utl_file" is the only way out for for the task to insert row with one clob value, when this value is real long. Or not insert clob cloumn to store it in DB - instead of that to store it in file on the server. I understand correctly? (drew attention to that I can real confused in pl sql and current theme) — Artem.Borysov, Jun 14 '15 at 22:44
@Artem.Borysov - if you can get the file onto the server you can [use an external table](https://oracle-base.com/articles/10g/external-tables-containing-lob-data) rather than trying to create your own solution. This is why it matters where the text is coming from and whether it can be in a file on the server. — Alex Poole, Jun 15 '15 at 10:06
Your version of the organization is working well, understood it. But at first, I guess, it would be more convenient to use the following script (CREATE TABLE lob_tab) http://my-oracle.it-blogs.com.ua/post-61.aspx#loadfromfile and start thinking about the what benefits can be gained from switching current DDL to external tables. A solution which should solve notification about too long string is to load text from the file — Artem.Borysov, Jun 15 '15 at 12:58

score 0 · Answer 1 · answered Jun 14 '15 at 12:42

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>

score 0 · Answer 2 · answered Jun 15 '15 at 13:07

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

Artem.Borysov · Accepted Answer · 2015-06-16T20:44:16.807

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

"--attention: needed" in accordance with my next question http://stackoverflow.com/questions/30873543/clob-value-in-out-return-from-plsql-invalid-lob-locator-specified-ora-22275 — Artem.Borysov, Jun 16 '15 at 20:38

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

3 Answers3