Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've an application currently producing reports using UTL_FILE and DIRECTORY. We are migrating this application to an environment in which there will be no filesystem access on the database server (including mounts).

One option is to use SQL*Plus on the client to produce the file.

Any suggestions for a low-key reporting tool designed for producing text-file output?

Thanks for any help.

share|improve this question
add comment

4 Answers 4

You could email them with UTL_MAIL or POST them to another server with UTL_HTTP

Alternatively, save the complete reports as a clob in a table and query from any client - this is a little more work as you'll have to rewrite your reporting procedures, but is still easier than starting from scratch on the client.

share|improve this answer
    
Thanks for the reply, but the reports need to end up on the application server file system. Although we could email to a server mail account this doesn't feel like the right solution. –  Nick Pierpoint Aug 22 '11 at 16:50
    
how about an HTTP POST? –  Jack Douglas Aug 22 '11 at 16:57
    
Another nice idea, but I'm looking for a 'pull' solution really. The database server is pretty much locked down. –  Nick Pierpoint Aug 22 '11 at 17:01
add comment

I know it would be easier to find something pre-built, but you could write a small application in your favorite language to handle the simple task of asking the database for the data and writing it to a file. For this you should take advantage of the suggestion from Jack Douglas to modify the reporting procedures to create CLOBs. Your app could then retrieve the CLOB and write the file.

share|improve this answer
    
Thanks Leigh. This is probably where I'm going to end up. Again - not normally the best approach but well-suited to this specific issue. Thanks. –  Nick Pierpoint Aug 23 '11 at 14:46
add comment

Consider ETL software (eg Pentaho kettle) as it is intended to extract data from databases

share|improve this answer
    
Thanks for the response. A good solution - I've also looked at using Jasper Reports. Probably overkill for my immediate issue but a good solution nonetheless. –  Nick Pierpoint Aug 23 '11 at 14:44
add comment

It would probably be overkill, but you could install Oracle Express Edition and use database links to have it retrieve the data to be written from the remote database. You would probably want the logic to remain on the existing server and just have the XE do the file writing. This method would allow you to re-use almost all your code, but it has the downside of requiring an additional installation of Oracle and the associated maintenance.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.