xmldom.WriteToClob doesn't work !!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: xmldom.WriteToClob doesn't work !!

  1. #1
    Join Date
    Nov 2000
    Location
    New Delhi, India
    Posts
    81

    Angry xmldom.WriteToClob doesn't work !!

    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)

    PHP Code:
    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

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    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

  3. #3
    Join Date
    Nov 2000
    Location
    New Delhi, India
    Posts
    81
    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

  4. #4
    Join Date
    Aug 2012
    Posts
    1

    Thanks

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width