Home » SQL & PL/SQL » SQL & PL/SQL » XML not saving in a particular directory (Oracle 11g, windows XP SP3)
XML not saving in a particular directory [message #590303] Tue, 16 July 2013 23:36 Go to next message
5vinod
Messages: 12
Registered: June 2012
Location: chennai
Junior Member
Hi All,
I want to create a xml file for a particular query and i have used the following code:
SELECT DBMS_XMLGEN.getXML('SELECT * FROM EMPL_NEW') FROM dual:

the output of this is
<?xml version="1.0"?>
<ROWSET>
<ROW>
<ENO>10001</ENO>
<ENAME>rajkumar</ENAME>
<ESAL>10000</ESAL>
</ROW>
<ROW>
<ENO>10002</ENO>
<ENAME>rajkumar</ENAME>
<ESAL>10000</ESAL>
</ROW>
<ROW>
<ENO>10003</ENO>
<ENAME>rajkumar</ENAME>
<ESAL>10000</ESAL>
</ROW>
</ROWSET>

and i need to save this as .xml file in a particular file directory in client machine and i have coded like this

DECLARE
F UTL_FILE.FILE_TYPE;
MYCLOB CLOB;
DIR_STR varchar2(100);
BEGIN
DIR_STR:='XML';
SELECT
DBMS_XMLGEN.GETXML('
SELECT
ENO,ENAME,ESAL
FROM
EMPL_NEW where rownum<=10
')
INTO MYCLOB
FROM DUAL;
F := UTL_FILE.FOPEN(DIR_STR,'EMP.XML','w',32767);
UTL_FILE.PUT(F,MYCLOB);
UTL_FILE.FCLOSE(F);
END;

XML is the name of directory name i have created in my user and I have also created the directory manually in D:/xml.

UTL_FILE package access given to public..

but still this error(refer the pic) exists!! anybody help me in this regard.

./fa/10990/0/

Thanks in Advance
Vinoth Kumar
  • Attachment: error.jpg
    (Size: 44.99KB, Downloaded 115 times)
Re: XML not saving in a particular directory [message #590304 is a reply to message #590303] Tue, 16 July 2013 23:41 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
UTL_FILE can only write to file system that is local to the DB Server; which excludes any client system.
Re: XML not saving in a particular directory [message #590305 is a reply to message #590304] Tue, 16 July 2013 23:51 Go to previous messageGo to next message
5vinod
Messages: 12
Registered: June 2012
Location: chennai
Junior Member
ok thanks!! how to write to DB SERVER!! can u help me from my code!!!
Re: XML not saving in a particular directory [message #590306 is a reply to message #590305] Tue, 16 July 2013 23:57 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
>how to write to DB SERVER!!
UTL_FILE requires OS folder with OS permission that allow Oracle to write files to it.

>can u help me from my code!!!
no
Re: XML not saving in a particular directory [message #590307 is a reply to message #590303] Wed, 17 July 2013 00:02 Go to previous message
Michel Cadot
Messages: 59293
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ There is no need to post an image for a simple text

2/
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

3/
Quote:
ow to write to DB SERVER!!


Like you did (as far as I can see on an unformatted code)!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Regards
Michel

Previous Topic: Invalid File Operation
Next Topic: Help me with the PL/SQL code to update the table in 11g
Goto Forum:
  


Current Time: Thu Oct 02 05:03:37 CDT 2014

Total time taken to generate the page: 0.09333 seconds