Oracle PLSQL中的LOB操作

Question

有人能指出这段代码有什么问题吗？

declare 
locations CLOB;
offset NUMBER :=1;
begin
  if locations is null then 
    dbms_output.put_line('location is null');
  else
    dbms_output.put_line('location is not null');
  end if;
  --assign an empty CLOB
  locations := empty_clob();
  --locations := dbms_lob.createTemporary();

  if locations is null then 
    dbms_output.put_line('location is null');
  else
    dbms_output.put_line('location is not null and length of the lob is: '||dbms_lob.getlength(locations));
  end if;
  --write something
  dbms_lob.open(locations,dbms_lob.lob_readwrite);
  dbms_lob.write(locations,length('i am not alone'),1,'i am not alone');
  --add more lines
  dbms_lob.writeappend(locations,length('i am not alone'),'i am not alone');
  dbms_lob.close(locations);

end;
/

我的错误是22275。00000 -“指定的无效LOB定位器”不确定为什么要这样做，因为我已经使用EMPTY_CLOB()函数初始化了CLOB“位置”。

Answer 1

文档empty_clob()注意到了这个限制：

不能将此函数返回的定位器用作DBMS_LOB包或OCI的参数。

如果您遵循该部分的链接，你也可以看到

运行EMPTY_BLOB()或EMPTY_CLOB()函数本身不会引发异常。但是，使用设置为空的LOB定位器来访问或操作任何PL/SQL DBMS_LOB或OCI函数中的LOB值都会引发异常。 可以使用空LOB定位器的有效位置包括INSERT语句的INSERT子句和UPDATE语句的SET子句。

您似乎试图使用createtemporary，因为它被注释掉了；但是您已经将它称为函数，而不是过程。您可以使用此表单来代替：

dbms_lob.createtemporary(locations, false);

所以：

set serveroutput on

declare 
  locations CLOB;
  offset NUMBER :=1;
begin
  if locations is null then 
    dbms_output.put_line('location is null');
  else
    dbms_output.put_line('location is not null');
  end if;
  --assign an empty CLOB
--  locations := empty_clob();
--  locations := dbms_lob.createTemporary();
  dbms_lob.createtemporary(locations, false);

  if locations is null then 
    dbms_output.put_line('location is null');
  else
    dbms_output.put_line('location is not null and length of the lob is: '||dbms_lob.getlength(locations));
  end if;
  --write something
  dbms_lob.open(locations,dbms_lob.lob_readwrite);
  dbms_lob.write(locations,length('i am not alone'),1,'i am not alone');
  --add more lines
  dbms_lob.writeappend(locations,length('i am not alone'),'i am not alone');
  dbms_lob.close(locations);

  -- added this for fun
  if locations is null then 
    dbms_output.put_line('location is null');
  else
    dbms_output.put_line('location is not null and length of the lob is: '||dbms_lob.getlength(locations));
  end if;

end;
/

这将获得输出：

location is null
location is not null and length of the lob is: 0
location is not null and length of the lob is: 28


PL/SQL procedure successfully completed.

Answer 2

这段代码意味着获得一个名为empty lob的系统常量，并尝试将其他内容写入其中。但失败了。为什么？:)

用locations := empty_lob()替换dbms_lob.createTemporary(locations, true);，您的代码就可以工作了。