?
Solved

PL/SQL to create an XMLFIle

Posted on 2004-11-22
10
Medium Priority
?
1,062 Views
Last Modified: 2011-09-20
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?
0
Comment
Question by:jbhate
  • 5
  • 4
10 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12646076
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
 

Author Comment

by:jbhate
ID: 12646654
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12647018
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jbhate
ID: 12647166
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12647902
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
 
LVL 2

Assisted Solution

by:Tony_Hasler
Tony_Hasler earned 300 total points
ID: 12651254
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12654735
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
 

Author Comment

by:jbhate
ID: 12656771
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
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1200 total points
ID: 12657154
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
 

Author Comment

by:jbhate
ID: 12706712
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month16 days, 6 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question