Calling a CLOB Locator Fails With ORA-22275 [ID 161530.1]

		修改时间 22-JUL-2009     类型 PROBLEM     状态 PUBLISHED

In this Document
  Symptoms
  Changes
  Cause
  Solution

---

Applies to:

PL/SQL
This problem can occur on any platform.

Symptoms

Running PLSQL code containing call to EMPTY_CLOB() fails with

ORA-22275: invalid LOB locator specified

Changes

Sample user "scott" must be installed on the test machine running the code shown below.
The user "scott" can be installed by connecting as SYS or SYSTEM user an run the following scripts to be found on the RDBMS Server depending on operating system

• Linux / Unix : $ORACLE_HOME/rdbms/admin/utlsampl.sql
• Windows     : %ORACLE_HOME%\rdbms\admin\utlsampl.sql

Cause

A LOB that is passed to package DBMS_LOB cannot be a variable initialized by EMPTY_CLOB() as this does not create a valid lob locator.
It must either be initialized by selecting the clob from the database or by using DBMS_LOB.CREATETEMPORARY

Solution

Example how to avaid the ORA-22275

connect scott/tiger

set serveroutput on

create or replace procedure test_clob (p_clob_res out clob) is
  cursor c_tabs is
  select ename from emp;
  v_clob clob;
  amt integer := 0;
begin
  dbms_lob.createtemporary(v_clob,true,dbms_lob.session);
  for r_tabs in c_tabs
  loop
    dbms_lob.writeappend(v_clob,length(r_tabs.ename)+1,r_tabs.ename||' ');
    amt := amt + length(r_tabs.ename);
  end loop;
  p_clob_res := v_clob;
end test_clob;
/

create or replace procedure call_clob is
  p_clob clob;
  my_buff varchar2 (2000);
  amt binary_integer := 2000;
begin
  test_clob(p_clob);
  my_buff := dbms_lob.substr(p_clob,amt,1);
  dbms_output.put_line(my_buff);
end call_clob;
/

begin
  call_clob();
end;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11411056/viewspace-734475/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11411056/viewspace-734475/