-
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
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|