Oracle DBMS_LOB: Using DBMS_LOB.convertToBlob multiple times in PLSQL block

Question

When I try to use DBMS_LOB.convertToBlob procedure multiple times in the same PLSQL block only the first execution works as expected. All additional executions produce empty blobs. Here is an example:

DECLARE 

v_temp1 varchar2(32767) := 'absd'; 
V_temp1_blob BLOB; 
V_temp2 varchar2(32767) := 'efghi'; 
V_temp2_blob BLOB;

v_in      integer := 1;
v_out     integer := 1;
v_lang    integer := 0;
v_warning integer := 0;

BEGIN

DBMS_LOB.createTemporary (V_temp1_blob, TRUE);  
DBMS_LOB.convertToBlob(V_temp1_blob, V_temp1, DBMS_LOB.LOBMAXSIZE, v_in, v_out, DBMS_LOB.DEFAULT_CSID, v_lang, v_warning);
dbms_output.put_line('V_temp1_blob: ' || dbms_lob.getlength(V_temp1_blob));

DBMS_LOB.createTemporary (V_temp2_blob, TRUE);
DBMS_LOB.convertToBlob(V_temp2_blob, V_temp2, DBMS_LOB.LOBMAXSIZE, v_in, v_out, DBMS_LOB.DEFAULT_CSID, v_lang, v_warning);
dbms_output.put_line('V_temp2_blob: ' || dbms_lob.getlength(V_temp2_blob));

END;

OUTPUT:

V_temp1_blob: 4
V_temp2_blob: 0

My expected output is:

V_temp1_blob: 4
V_temp2_blob: 5

What am I missing here ?

Justin Cave · Accepted Answer · 2015-08-09 08:02:22Z

Your v_in and v_out local variables are being passed in as the dest_offset and src_offset parameters of the convertToBlob procedure. These are in out parameters so they are modified by the call.

dest_offset, which is what you're passing v_in for, is defined to be

(IN)Offset in bytes in the destination LOB for the start of the write. Specify a value of 1 to start at the beginning of the LOB.

(OUT)The new offset in bytes after the end of the write.

while src_offset, which is what you're passing v_out for, is defined to be

(IN)Offset in characters in the source LOB for the start of the read.

(OUT)Offset in characters in the source LOB right after the end of the read.

For the first call, you're passing in 1 in both cases. For the second call, you're passing in the offsets from the first LOB. You'd need to re-initialize both parameters to 1 before making the second call.

I'd also suggest that you probably want to choose better variable names than v_in and v_out for these variables. If they were named v_src_offset and v_dest_offset, that would generally make your code clearer and might make the bug easier to spot.

Collectives™ on Stack Overflow

Oracle DBMS_LOB: Using DBMS_LOB.convertToBlob multiple times in PLSQL block

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related