Oracle DBMS_LOB:在PLSQL块中多次使用DBMS_LOB.convertToBlob [英] Oracle DBMS_LOB: Using DBMS_LOB.convertToBlob multiple times in PLSQL block

问题描述

当我尝试在同一PLSQL块中多次使用DBMS_LOB.convertToBlob过程时，只有第一次执行可以按预期进行.所有其他执行都会产生空的Blob.这是一个示例:

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;

输出:

V_temp1_blob: 4
V_temp2_blob: 0

我的预期输出是:

V_temp1_blob: 4
V_temp2_blob: 5

我在这里想念什么?

推荐答案

您的v_in和v_out局部变量将作为convertToBlob过程的dest_offset和src_offset参数传入.这些是in out参数，因此可以通过调用进行修改.

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，即您要传递v_in的内容，被定义为

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

(IN)在目标LOB中以字节为单位的偏移量，用于开始写入. 将值指定为1可以从LOB的开头开始.

(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)写入结束后的新偏移量(以字节为单位).

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

而src_offset是您要传递的v_out的定义为

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

(IN)源LOB中的字符偏移量，以开始读取.

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

(OUT)源LOB中字符的偏移量 阅读.

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

对于第一个电话，两种情况下您都传递了1.对于第二个调用，您要传入第一个LOB的偏移量.在进行第二次调用之前，您需要将两个参数都重新初始化为1.

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.

我还建议您为这些变量选择比v_in和v_out更好的变量名.如果将它们分别命名为v_src_offset和v_dest_offset，通常可以使您的代码更清晰，并可能更容易发现该错误.

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.

这篇关于Oracle DBMS_LOB:在PLSQL块中多次使用DBMS_LOB.convertToBlob的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！