Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-22275 Invalid lob locator when using CLOB from a view

user518536Jul 4 2006 — edited Jul 5 2006

Hi,

I am having problems when passing a CLOB from a "Union all" view to a function. I get an ORA-22275 error when trying to construct an XmlType from the CLOB and
the CLOB originates from a view. If the CLOB originates from a table, eveyting works fine. Here is the code, that reproduces the problem

  
CREATE TABLE testclob
    (field1                         CLOB)

/

-- insert some data
insert into testclob values ('<a/>');
/

-- Define a clob view over some tables

create or replace view v_testclob 
(field1)
as
select field1 from testclob
union all
select field1 from testclob; -- in reallity I use different tables

/

-- Creat a functions that proceses the CLOB

CREATE OR REPLACE 
function MyFunction(v_myClob clob) return VARCHAR2
IS
   myXML XMLTYPE;
BEGIN

  select xmltype(v_myClob) into myxml from dual; -- the view crashes ** HERE **
  -- code ommited
  return 'some data';
END;

/
-- Try to use the function:
-- Selecting from a table works OK
select myfunction(field1) from testclob;


-- Selecting from the view crashes
select myfunction(field1) from v_testclob;

Error: ORA-22275: invalid LOB locator specified ORA-22275: invalid LOB locator specified ORA-06512: at "D_TEST.MYFUNCTION", line 6


-- I using the following version:
select * from v$version;

-- Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production       
-- PL/SQL Release 9.2.0.1.0 - Production                            
-- CORE	9.2.0.1.0	Production                                        
-- TNS for 32-bit Windows: Version 9.2.0.1.0 - Production           
-- NLSRTL Version 9.2.0.1.0 - Production 

The only workaround I have found is to use substr

  select xmltype(dbms_lob.substr(v_myClob)) into myxml from dual; -- workaround

.. but this mght trucnate my data.

What am I doing wrong?
Matej

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 2 2006
Added on Jul 4 2006
2 comments
529 views