xmldom.WriteToClob doesn't work !!

[ShrutiM]

Hello,

I'm trying to get some information from database(oracle 10g/9i) create a DOMDocument out of it. and finally intend to write this Document as XML file on disk.

the DOMDocument gets created and the function CLOB object also gets written to without error. but when I try to use this CLOB to write to disk it gives this error:

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified:
ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 759
ORA-06512: at line 56

I'm also putting up the code below though modified it a bit to save space (removed database interaction and just creating a hard-coded xml DOMDocument)

declare
   XML_Doc          xmldom.DOMDocument;
   Main_Node        xmldom.DOMNode;
   Meta_Element     xmldom.DOMElement;
   Meta_Node        xmldom.DOMNode;
   DBObject_Element xmldom.DOMElement;
   DBObject_Node    xmldom.DOMNode;

   XML_CLOB         CLOB         := NULL;

      vFileName      VARCHAR2 (30) := 'MetaData.xml';
        -- The xml filename on disk to write to
      vDirectory     VARCHAR2 (30) := 'XML_DIR'; 
        --Created the above directory as 
        --   'Create Directory XML_DIR as 'E:\oracle\utl_dir';

      vOut_File      UTL_FILE.File_type;
      
      vBuffer            VARCHAR2(32767);
      vBuffer_Size       CONSTANT BINARY_INTEGER := 32767;
      vAmount            BINARY_INTEGER;
      vOffset            NUMBER(38);

begin
     
      -- ------------------------------------------------------------
      -- Generate a DOMDocument and putting it into CLOB
      -- ------------------------------------------------------------
      XML_Doc := XMLDOM.NewDOMDocument;
      Main_Node := XMLDOM.MakeNode(XML_Doc);

      Meta_Element := XMLDOM.createElement(XML_Doc, 'MetaData');
      Meta_Node := XMLDOM.AppendChild(Main_Node, 
                              XMLDOM.MakeNode(Meta_Element));
      DBObject_Element := XMLDOM.createElement(XML_Doc, 'DBObject');
      XMLDOM.setAttribute(DBObject_Element, 'CommitStatus', 'N');
      XMLDOM.setAttribute(DBObject_Element, 'Name', 'My Object Name');
      DBObject_Node := XMLDOM.AppendChild(Meta_Node, 
                              XMLDOM.MakeNode(DBObject_Element));

      DBMS_XMLDOM.writeToCLOB(XML_Doc, XML_CLOB);
         
      XMLDOM.freeDocument(XML_Doc);

      -- ------------------------------------------------------------
      -- OPEN NEW XML FILE IN WRITE MODE
      -- ------------------------------------------------------------
      vOut_File := UTL_FILE.FOPEN(
                     location     => vDirectory,
                     filename     => vFileName,
                     open_mode    => 'w',
                     max_linesize => vBuffer_Size);

      vAmount := vBuffer_Size;
      vOffset := 1;

      -- ------------------------------------------------------------
      -- READ FROM CLOB XML / WRITE OUT NEW XML TO DISK
      -- -------------------------------------------------------------
      WHILE vAmount >= vBuffer_Size
      LOOP
         DBMS_LOB.READ(
                     lob_loc    => XML_CLOB,
                     amount     => vAmount,
                     offset     => vOffset,
                     buffer     => vBuffer);
         vOffset := vOffset + vAmount;

         UTL_FILE.PUT(
                     file      => vOut_File,
                     buffer    => vBuffer);

         UTL_FILE.FFLUSH(file => vOut_File);
      END LOOP;
      
      UTL_FILE.FCLOSE(file => vOut_File);
end;
/ 

Thanks.

With Regards,
./shruti

[hrishy]

Hi

Does this directory E:\oracleutl_dir exist ?

also try from sqplus creating

create directory E:\oracleutl_dir

and then try and let us know

regards
Hrishy

[ShrutiM]

the directory existed and was valid.

I found the problem, it was because
XML_CLOB had not been initialised:

It worked, when I did:
DBMS_LOB.CreateTemporary(XML_CLOB, TRUE);

thanks and regards,

./shruti

[Carlos Brand]

Thanks Shruti for your replay. it helps me with the same error. Thanks a lot.