Generating xml file from query using oracle 10 and plsql

Hi, I need to generate an xml file from a stored procedure, and retriving it by a java resultset.
My database is Oracle 10 that, as far as I know, supports this kind of features.
I have few questions:
1) is it possible to perform such operation "on the fly", or it is necessary to write the file first and then read it? I mean, for ex. call the procedure and get file content as string.
2) is it performing? Of course assuming that the query that generates the dataset is.
3) In the examples I found, the procedure generates always a temp table. Is it possible to skip this step?
Thanks in advance
Who is Participating?
slightwv (䄆 Netminder) Commented:
1) sure:  there are several ways depending on the exact requirements.  for example dbms_xmlgen.get_xml, or use the various XML sql functions like XMLELEMENT,XMLFOREST, etc...

2) 10g seems to be pretty quick generating XML but I'm not sure what you would be comparing it with.

3) Should be but that depends on the complxity of the XML you want to generate and the tables involved.  The XML functions referenced above can generate some pretty complex XML.
can you provide some sample data and expected output?
we could provide an example of how to achieve the results
hanoirulesAuthor Commented:
thanks for the answers!
I was trying dbms_xmlgen.get_xml; it seems performing.
Do you know if there is any limit on the buffer, or anything else I should be aware of?
Any particulary cautions I have to be concerned about?
Thanks a lot :)
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.

hanoirulesAuthor Commented:
Sorry sdstuber, I did not answer.
Thanks for the offer :)
It is important for me to find out all pros and cons in adopting this solution, much more than write the procedure :)
thanks again
slightwv (䄆 Netminder) Commented:
The getxml functipon returns an Oracle CLOB so as long as the generated XML is less than 4Gig, you should be OK.

You just need to make sure the Java calls can handle a CLOB and not treat it as a VARCHAR2.

I'm not a Java person so I cannot help with that end.  I've seen a lot of posts on Java and CLOBs when Googling but cannot suggest any.
Don't know about original XML question,
 but looking at the last post, if there is any relevance, this is how I read CLOB from Oracle 9 ytable
in Java:

    public static String retrieve_string_from_clob (String axp,
                                 PreparedStatement pst){

       //PreparedStatement : "select  my_clob_column
       //  from axp_clobs where  axp = ?";

       String molString = null;

          try {

          pst.setString(1, axp);

              ResultSet results=pst.executeQuery();


                  String clob = results.getString(1);

                  if(clob == null) return null;

              Reader reader=new StringReader(clob);
              CharArrayWriter writer=new CharArrayWriter();
              int i=-1;

              while ( (!=-1)
                  molString =    new String(writer.toCharArray());

          catch(Exception ex){
              System.out.println("Error " + ex.toString() + " " + axp);


          return molString;

Open in new window

hanoirulesAuthor Commented:
Thanks to everybody, I'll try to devide the points in the best way :)
Really thanks for the help :)
I appreciate your generosity

but, please don't award "participation" points.

My post was simply a request for information, the others gave usable feedback.

I'm a zone advisor, I have the power to reopen the question.
I'd like to do so and allow you to rescore and give my points to whichever posts were most helpful.

simply reply yes and I'll make it so.

Thanks again
> but, please don't award "participation" points.

Happens all the time unfortunately

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.

All Courses

From novice to tech pro — start learning today.