CODEWITHSUNDEEP
plsqlblob
Lupuss
Lupuss

Reputation: 649

Can't use PLSQL ConvertToBlob inside a loop

I have a problem when trying to convert CLOB from a table to BLOB into another table. Basically I'm looping inside a PLSQL array, the first call to DBMS_LOB.convertToBlob always works well, but the next iterations either create an empty blob or give me an error ORA-22275: invalid LOB locator specified, depending on whether I initialize my blob inside or outside the loop.

So, if I do :

BEGIN
FOR i IN 1 .. rs.COUNT
LOOP
DBMS_LOB.createTemporary (v_blob, TRUE);
DBMS_LOB.convertToBlob (v_blob,
rs (i).v_clob,
DBMS_LOB.LOBMAXSIZE,
v_in,
v_out,
DBMS_LOB.DEFAULT_CSID,
v_lang,
v_warning);
[...]
DBMS_LOB.freeTemporary(v_blob);

It converts the first blob well but only returns empty blobs for the other ones.

If I do:

BEGIN
DBMS_LOB.CREATETEMPORARY (v_blob, TRUE);
FOR i IN 1 .. rs.COUNT
LOOP
DBMS_LOB.convertToBlob(...);

It also converts the first blob well but I get the ORA-22275: invalid LOB locator specified error after the first iteration.

How could I avoid this? I can't seem to find good explanation for this. Thanks for your help!

Upvotes: 1

Views: 2269

Answers (5)

Ekon
Ekon

Reputation: 1

I had a similar problem to this and I figured something about DBMS_LOB.convertToBlob. The variables that you enter as dest_offset and src_offset change after the go through the procedure in question so you have to reset them after every iteration, in the case that you want to create multiple files.

I don't know if that helps directly with this problem in particular, but keep it in mind, for future reference, in case one uses DBMS_LOB.convertToBlob inside a loop.

Upvotes: 0

ihm017
ihm017

Reputation: 182

This error might occur when the CLOB contains NULL. This means the CLOB variable is passed NULL to the DBMS_LOB.convertToBlob

Upvotes: 0

Ken Madsen
Ken Madsen

Reputation: 21

This worked for me:

declare
  cursor note is
    select id, rtf_clob
      from rtf_data
     where rtf_clob is not null
    for update of rtf_blob;
  l_blob        blob;
  l_amt         integer := dbms_lob.lobmaxsize;
  l_dest_offset integer := 1;
  l_src_offset  integer := 1;
  l_csid        integer := dbms_lob.default_csid;
  l_ctx         integer := dbms_lob.default_lang_ctx;
  l_warn        integer;

begin
  for note_rec in note loop
    l_blob        := null;
    l_amt         := dbms_lob.lobmaxsize;
    l_dest_offset := 1;
    l_src_offset  := 1;
    l_csid        := dbms_lob.default_csid;
    l_ctx         := dbms_lob.default_lang_ctx;
    l_warn        := null;
    dbms_lob.createTemporary(l_blob, true);
    dbms_lob.convertToBlob(l_blob,
                           note_rec.rtf_clob,
                           l_amt,
                           l_dest_offset,
                           l_src_offset,
                           l_csid,
                           l_ctx,
                           l_warn );

    update rtf_data
       set rtf_blob = l_blob
     where note_rec.id = id;

    dbms_lob.freeTemporary(l_blob); 
  end loop;
end;
/

Without reinitializing the variables inside the loop, only the first record created a blob.

Upvotes: 0

James Nolan
James Nolan

Reputation: 1

I experienced a similar problem when using temporary blobs within a loop. I resolved it by initialising the in, out, lang and warning parameters each time.

James.

Upvotes: 0

hol
hol

Reputation: 8423

Must be a NULL value problem. The CLOB must not be NULL. The following code gives me the error on the third round.

set serveroutput on
declare 
   TYPE rs_rec_type IS RECORD (
      v_clob clob
      );
  TYPE rs_rec_table_type IS TABLE OF rs_rec_type INDEX BY pls_integer;
  rs   rs_rec_table_type;
  v_blob    blob;
  v_in      integer := 1;
  v_out     integer := 1;
  v_lang    integer := 0;
  v_warning integer := 0;
BEGIN
  rs(1).v_clob := 'foo';
  rs(2).v_clob := 'bar';
  rs(3).v_clob := null;
  FOR i IN 1 .. rs.COUNT
  LOOP
      DBMS_LOB.createTemporary (v_blob, TRUE);
      dbms_output.put_line('i='||i);  
      DBMS_LOB.convertToBlob (v_blob,
                             rs (i).v_clob,
                             DBMS_LOB.LOBMAXSIZE,
                             v_in,
                             v_out,
                             DBMS_LOB.DEFAULT_CSID,
                             v_lang,
                             v_warning);
      dbms_output.put_line('done i='||i); 
      DBMS_LOB.freeTemporary(v_blob);     
  end loop;
end;

Output

Error report:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 991
ORA-06512: at line 20
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    
*Action:
i=1
done i=1
i=2
done i=2
i=3

Upvotes: 2

Related Questions