Is there a better way to do this - dbms_sql

        I've been asked to put together an application that will take a query, run it in the back ground, and then make the result set available to the requestor at a later date. We don't know what the query will be until it is raised, so I think we are firmly into dbms_sql territory. What I'm doing is using the dbms_sql.column_value procedures to get each value, and then appending the value to a CLOB (with odd bits of wrapping to make it look like a csv file), and the CLOB is then written back to the database, ready for the user to recover at a time of their choosing.

        I've got something working, and the users are happy with how it works, apart from one thing, the speed. If I run a non-trivial query from the command line, I get the results back in maybe 30 seconds (for ~100,000 rows), running the same query in the background is taking 6-7 minutes. For queries that return a low number of rows the time taken is pretty much the same which ever way we do it. Our suspicion is that the extra time is taken up by the appending it to the CLOB, rather than the query itself. Is there any practical way of speeding this up ?

Thanks in advance

Adrian

-- 
To Reply :
replace "bulleid" with "adrian" - all mail to bulleid is rejected
Sorry for the rigmarole, If I want spam, I'll go to the shops
Every time someone says "I don't believe in trolls", another one dies.