Oracle temporary table access error

Question

I have a GLOBAL TEMPORARY table in Oracle. It uses ON COMMIT DELETE ROWS. One of the columns in the table is an XMLType column. I have used GLOBAL TEMP tables quite a bit...with success. However, after introducing the XMLType columne and running a function against the TEMP table I get this error message:

ORA-14453: attempt to use a LOB of a temporary table

--This code (which is located in a function) barfs. THE_TABLE is the temp table containing the XMLType column and THE_ROWS is a collection object

   DECLARE v_table            a_collection_table;


   SELECT mcs2.THE_ROWS (  
                  xml, f1, f2 )
   BULK COLLECT INTO v_table
     FROM (SELECT *
             FROM THE_TABLE) a;


   -- Executing a commit flushes the records
   -- for the temp table for this session
   COMMIT;
   --
   RETURN v_table;

--This code works after removing the XMLType column of course, I need the XML column, and can accomplish this using a seperate temp table with an XML column and doing some work to parse it out.... I was just curious as to the cause

   DECLARE v_table           a_collection_table;

   SELECT mcs2.THE_ROWS (  
                  f1, f2 )
   BULK COLLECT INTO v_table
     FROM (SELECT *
             FROM THE_TABLE) a;


   -- Executing a commit flushes the records
   -- for the temp table for this session
   COMMIT;
   --
   RETURN v_table;

Anybody have any ideas? Thanks

Answer 1

Sounds like you are trying to use the XML data after committing the transaction. A fuller example (table structure, insert and execution) might help.

But as an example :

create global temporary table test_tt (id number, x xmltype) on commit delete rows;

insert into test_tt values (1,
'<?xml version="1.0"?> <ROWSET>  <ROW>   <DUMMY>X</DUMMY>  </ROW> </ROWSET>');

select extract(x,'/ROWSET/ROW/DUMMY') from test_tt;

commit;
declare
  v_xml xmltype;
begin
  insert into test_tt values (1,
    '<?xml version="1.0"?> <ROWSET>  <ROW>   <DUMMY>X</DUMMY>  </ROW> </ROWSET>');
  select x into v_xml from test_tt;
  commit;
  insert into test_tt values (2,v_xml);
end;
/

The standalone select works fine.

The PL/SQL errors out with "ORA-08103: object no longer exists", which is similar to the ORA-14453. The v_xml is partly/mostly a pointer to the LOB. Remember, LOBs can be gigbytes in size, so they are not fully materialized into memory. Once the commit happens, it is a pointer to something that no longer exists.