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

ljm0211發表於2012-07-03
AI
  修改時間 22-JUL-2009     型別 PROBLEM     狀態 PUBLISHED  

In this Document
  
  
  
  

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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章