Oracle write to file

Question

I am running oracle and have a query which pulls some results from the database. I would like to write the results as a text file. How would I go about doing this?

My prefered way would be by using UTL_FILE. Would some one have an example of how to do this?

Answer 1

If you're running the query from sqlplus you can use the spool command:

spool /tmp/test.spool

After executing the spool command within a session, all output is sent to the sqlplus console as well as the /tmp/test.spool text file.

Answer 2

If you're using Sql Plus, is as easy as:

SQL> spool c:\temp\out.txt
SQL> SELECT * FROM USERS;
SQL> spool off

This three sentences will output the result of the query "SELECT * FROM USERS" to the file c:\temp\out.txt.

You can format this query using the string manipulation functions of Oracle.

Answer 3

If you are using PL/SQL then you can use the UTL_FILE package, the difference from using sql+ spool is that the files are written to the server file system. UTL_FILE has a number of limitations so an alternative on the server side would be to use Java stored procedures.

Answer 4

This seems to be a reasonable tutorial with a few simple examples UTL_FILE example

Answer 5

Use UTL_FILE in combination with CREATE DIRECTORY for ease of mapping a directory path with a name (it does not create the actual directory just a reference to it so ensure it is created first)

example

  create directory logfile as 'd:\logfile'; -- must have priv to do this

declare
  vFile utl_file.file_type;
begin
  vFile := utl_file.fopen(logfile ,'syslog','w'); -- w is write. This returns file handle
  utl_file.put(vFile,'Start Logfile'); -- note use of file handle vFile
  utl_file.fclose(vFile); -- note use of file handle vFile
end;