oracle判断clob为空,CLOB字段使用的一些测试。

测试代码：

一、第一组测试：

1、

declare

v_clob clob;

begin

dbms_lob.append(v_clob,'ss');

dbms_output.put_line(dbms_lob.getlength(v_clob));

v_clob := empty_clob();

dbms_output.put_line(dbms_lob.getlength(v_clob));

dbms_lob.append(v_clob,'ss');

dbms_lob.createtemporary(v_clob,true);

dbms_output.put_line(dbms_lob.getlength(v_clob));

end;

红色地方报错：ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275

2、

declare

v_clob clob;

begin

--dbms_lob.append(v_clob,'ss');

dbms_output.put_line(dbms_lob.getlength(v_clob));

v_clob := empty_clob();

dbms_output.put_line(dbms_lob.getlength(v_clob));

dbms_lob.append(v_clob,'ss');

dbms_lob.createtemporary(v_clob,true);

dbms_output.put_line(dbms_lob.getlength(v_clob));

end;

红色地方报错：

ORA-22275: invalid LOB locator specified

ORA-06512: at "SYS.DBMS_LOB", line 366

ORA-06512: at line 8

22275. 00000 -  "invalid LOB locator specified"

*Cause:    There are several causes

initialized; (2) the locator is for a BFILE and the routine

expects a BLOB/CLOB/NCLOB locator; (3) the locator is for a

BLOB/CLOB/NCLOB and the routine expects a BFILE locator;

(4) trying to update the LOB in a trigger body -- LOBs in

trigger bodies are read only; (5) the locator is for a

BFILE/BLOB and the routine expects a CLOB/NCLOB locator;

(6) the locator is for a CLOB/NCLOB and the routine expects

a BFILE/BLOB locator;

*Action:   For (1), initialize the LOB locator by selecting into the locator

variable or by setting the LOB locator to empty.  For (2),(3),

(5) and (6)pass the correct type of locator into the routine.

For (4), remove the trigger body code that updates the LOB value.

3、

declare

v_clob clob;

begin

--dbms_lob.append(v_clob,'ss');

dbms_output.put_line(dbms_lob.getlength(v_clob));

v_clob := empty_clob();

dbms_output.put_line(dbms_lob.getlength(v_clob));

--dbms_lob.append(v_clob,'ss');

dbms_lob.createtemporary(v_clob,true);

dbms_lob.append(v_clob,'ss');

dbms_output.put_line(dbms_lob.getlength(v_clob));

end;

执行成功。

结论：对于一些dbms_lob里的函数，传入的lob参数为NULL(定义一个lob变量，它的值为NULL)或EMPTY_CLOB()，都是会报错的。lob参数为临时对象或有值的，都是可以的。查阅文档，所有的dbms_lob函数

的参数都不能为NULL。

第二组测试：

1、declare

v_clob clob;

begin

if dbms_lob.getlength(v_clob) = 0 then

dbms_output.put_line('null');

else

v_clob :=empty_clob();

dbms_output.put_line(dbms_lob.getlength(v_clob));

end if;

end;

结果为：0，证明dbms_lob.getlength里如果传的为NULL，则其返回为NULL，而传入empty_clob()则返回为零。

第三组测试：

create table lob_test2(col number,col1 clob);

1、

begin

insert into lob_test2 values(1,null);

insert into lob_test2(col) values(2);

insert into lob_test2 values(3,empty_clob());

commit;

end;

select * from lob_test2;

1 null

2 null

3

2、

declare

v_clob clob:='sdf';

begin

update lob_test2

set col1 = v_clob

where col = 1;

update lob_test2

set col1 = v_clob

where col = 2;

update lob_test2

set col1 = v_clob

where col = 3;

commit;

end;

select * from lob_test2;

1 sdf

2 sdf

3 sdf

结论：对于表中定义的CLOB字段，无论是null还是empty_clob(),无论是新增还是更新都是可以直接操作的。

第四组测试：

1、从.net里传的clob参数，如果是空字符串，则传到ORACLE后，为一个empty_clob而不是null。

2、从.net里传的clob参数，如果是NULL，则传到ORACLE后，为null。

最后结论：在处理clob时，要先判断好，其为NULL还是EMPTY_CLOB(),还是有值的，这样可以保证函数或过程的健壮性。

判断方法：dbms_lob.getlength(v_clob) is null or dbms_lob.getlength(v_clob)=0则不能调用dbms_lob函数。