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.


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.

  • 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

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.

  • 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

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

  • 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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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