PL/SQL to create an XMLFIle

I am using PL/SQl to create an XML file. The procedure calls XMLDOM.writeToFile procedure to create the xml object to a file. The file should be created on the file server which is different than the database server. I am getting ORA-20000 when I try to create the xml file on the file server. If I do not give the file path, the file is created in tne ORACLE_HOME directory.
The part of my code that is giving the error is as follows:
   
  proj_dir  -- this variable contains the directory path to create the file on the server
   filename := proj_dir ||'\'||'XFRTEST.xml';
   xmldom.writeToFile(doc, filename);

 
If I modify the code as
  filename :=   'XFRTEST.xml';
 xmldom.writeToFile(doc, filename);
The file is created in the oracle_home directory , that is not desirable.
How do I create the file on the file server?
jbhateAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Oracle can only create files on the DB server (along the lines of UTL_FILE).  You can map the fileserver drive from the DB server or depending on the middleware (appilcation) you are calling the stored procedure from, you can have the procedure return a CLOB and let the application write the file to the appropriate server.
0
jbhateAuthor Commented:
Hi,
    Does that mean I should use WriteToClob procedure. Then how do I save it to a file. I appreciate any help with this problem.
0
slightwv (䄆 Netminder) Commented:
Please explain a little bit more about your setup.

i.e./ How you would like the process work/what applications are available/how you are calling the procedure/etc...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jbhateAuthor Commented:
We have a web deployed oracle forms application. Forms listener servlet is running on Jrun4.0
We want to call a database procedure from oracle forms, this procedure will accept certain parameters, extract data from the database and create an xml file. This file must be created on the file server.
0
slightwv (䄆 Netminder) Commented:
I believe that you will have to use the writetoCLOB and return the CLOB to your forms app for writing to the server.  I'm not a forms person so I will be unable assist you further (sorry).
0
Tony_HaslerCommented:
WARNING WARNING WARNING

I have never done anything with XML AND I have just finished an entire bottle of white wine

So take what I have to say with a grain of salt BUT:

I do not believe a word of this explanation.  No offence.

I cannot see how Oracle would care a hoot about the syntax of the path supplied for the file.  I see that ORA-20000 is an exception raised by the package.  I would suggest trapping this exception and finding the specific message associated with it.  My guess is that the filename is too long, or that there is some other reason that you cannot open the file.  Why not hardcode the full path, and see if that works?

Time for bed.  I hope this has been entertainment, if not expert help :-)
0
slightwv (䄆 Netminder) Commented:
Tony_Hasler:  Try to have a PL/SQL stored procedure write a file to the client machines hard drive........  I'd switch to red wine, white doesn't agree with you.
0
jbhateAuthor Commented:
Hi,
    Thanks for the suggestion Tony_Hasler. I checked the directory, it does exist on the file server.
 If I run this process in SQLPLUS I get the same error. ORA_20000 Invalid directory....
Again if I do not give the path then the file is created in the ORACLE_HOME. I tried mapping the file server to a drive on the database server, that did not work. Can it be true that PL/SQL has such a limitation as that? It can create file only on the database server, not even on the mapped drive.
 IS there any work around that?
Thanks.
0
slightwv (䄆 Netminder) Commented:
You can create a file on a drive mapped on the DB server.  There are issues with this:  There are bizarre permissions that need set up before you can write to these drives (Again, this is all from my knowledge of UTL_FILE and not writeToFile but I'm betting the issues are the same).

Check out:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:241814624807

Just the permisions section (not the UTL_FILE_DIR settings).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jbhateAuthor Commented:
Finally figured out a way to wite to the file server. Oracleservice and the listner had to be started as a network user instead of the LocalSystem.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.