OraFAQ Forum: JDeveloper, Java & XML » generate XML doc using DBMS

Home » Developer & Programmer » JDeveloper, Java & XML » generate XML doc using DBMS_XMLGEN and write it to a file system

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

generate XML doc using DBMS_XMLGEN and write it to a file system [message #91634]

Wed, 18 December 2002 18:51

Devang Pandya
Messages: 9
Registered: June 2002

Junior Member

Hi Friends,

I'm facing a big trouble for writting a XML doc generated by the DBMS_GENXML.getXML function (it retunrs xml doc as CLOB datatype) to the file system. I can not use utl_file because it does not support CLOB(i guess..I'm not sure abt that). I'm trying to convert that CLOB to varchar2 and then write it to file using utl_file pkg but it is giving me problem. Can some one give me the solution for this?

var my_xml clob
set autoprint on

declare
l_clob clob;
l_ctx dbms_xmlgen.ctxHandle;
l_sql varchar2(400);
flag boolean ;
begin
l_sql := 'select * from MESSAGE_TEMPLATE where rownum < 5';
l_ctx := dbms_xmlgen.newContext(l_sql);
:my_xml := dbms_xmlgen.getXml(l_ctx); -- or you could assign it to l_clob

end;

The above block give me perfect output on sqlplus but i want to write that output to some file.

Please Help !!!

thanks in advance

Report message to a moderator

Re: generate XML doc using DBMS_XMLGEN and write it to a file system [message #91636 is a reply to message #91634]

Thu, 19 December 2002 07:05

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

u can use UTL_FILE builtin package to write to OS files from PL/SQL.
make sure set the init parameter UTL_FILE_DIR.
You can also open the file in APPEND mode or WRITE mode.

Report message to a moderator

Re: generate XML doc using DBMS_XMLGEN and write it to a file system [message #91638 is a reply to message #91634]

Thu, 19 December 2002 07:41

Devang Pandya
Messages: 9
Registered: June 2002

Junior Member

UTL_FILE doesnt work for the LOB objects. Is there any other way to do it?

thanks

Report message to a moderator

Re: generate XML doc using DBMS_XMLGEN and write it to a file system [message #91724 is a reply to message #91634]

Tue, 21 January 2003 05:41

Sanjay
Messages: 236
Registered: July 2000

Senior Member

why not u r devlopening one normal report from report designer.
there is one option to convert the report output into xml.
i found that is to easy to do

Report message to a moderator

Re: generate XML doc using DBMS_XMLGEN and write it to a file system [message #92531 is a reply to message #91634]

Wed, 01 September 2004 23:58

Kumar
Messages: 115
Registered: December 1998

Senior Member

l_clob_len := DBMS_LOB.GETLENGTH(my_xml );
l_amount := 32767;
l_pos := 1;

-------------------------------------------------------------------
--Copy the contents of the CLOB to the File
--
-------------------------------------------------------------------
WHILE l_pos < l_clob_len
LOOP
DBMS_LOB.READ(l_xml_clob, l_amount, l_pos, l_Buffer);

IF l_Buffer IS NOT NULL THEN
-- write it to the File
UTL_FILE.PUT( l_handle, l_Buffer );
END IF;

l_pos := l_pos + l_amount;
END LOOP;

UTL_FILE.FCLOSE(l_handle);
DBMS_LOB.FREETEMPORARY(l_xml_clob);

Report message to a moderator

Previous Topic:	Installing DBMS_JAVA package
Next Topic:	Oracle specific types

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 23:55:20 CDT 2024