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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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:
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.

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
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 :)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
Mick BarryJava DeveloperCommented:
> but, please don't award "participation" points.

Happens all the time unfortunately

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.