CLOB problem in a Stored Function

Hi folks,

    I'm an absolute newbie to PL/SQL, so please forgive any obvious blunders!

    I'm using Oracle 8.1.7 and trying to write a PL/SQL Stored Function which will read a CLOB data column (via a cursor) and append it to a CLOB variable. Although the code compiles OK, it fails at runtime with "Invalid LOB locator specified". I have a table called "IBLOCKS" which a few VARCHAR2 columns plus a CLOB column called "XML" and I assumed that I could simply use DBMS_LOB.APPEND to achievewhat I need. Anyway here's the code...

CREATE OR REPLACE FUNCTION getPageXML2 (as_PageID IN VARCHAR2) RETURN CLOB

IS

  CURSOR lo_BlocksRowSet IS SELECT * FROM IPIVOT, IBLOCKS WHERE IPIVOT.PAGE_ID = as_PageID AND IPIVOT.BLOCK_ID = IBLOCKS.ID;

  lo_CLOB CLOB;

  ls_Temp VARCHAR2(4000);

BEGIN   lo_CLOB:= EMPTY_CLOB();

  DBMS_LOB.CREATETEMPORARY(lo_CLOB, TRUE );

  FOR lo_BlockRow IN lo_BlocksRowSet LOOP

    ls_Temp := '<Area name=''' || lo_BlockRow.PAGE_AREA || ''' description=''' || lo_BlockRow.DESCRIPTION || '''>';

   DBMS_LOB.WRITE(lo_CLOB, LENGTH(ls_Temp), DBMS_LOB.GETLENGTH(lo_CLOB) + 1, ls_Temp);

• ERROR HAPPENING ON THE FOLLOWING LINE: "INVALID LOB LOCATOR SPECIFIED": DBMS_LOB.APPEND(lo_CLOB, lo_BlockRow.XML);

    DBMS_LOB.WRITE(lo_CLOB, 7, DBMS_LOB.GETLENGTH(lo_CLOB) + 1, '</Area>');

     END LOOP;   RETURN lo_CLOB;

END;

    I assume what is going wrong is that IBLOCKS.XML is simply a pointer or reference to the CLOB rather than the CLOB itself...?

    Any assistance would be very much appreciated! :-)

-- 

Cheers,

Dave Matthews

'New Avengers' and 'Professionals' sites at:
http://www.mark-1.co.uk