5

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?

5 Answers5

5

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.

Jeff Atwood
  • 63,320
  • 48
  • 150
  • 153
Alotor
  • 7,407
  • 12
  • 38
  • 36
4

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.

stjohnroe
  • 3,168
  • 1
  • 27
  • 27
2

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;
Andrew Wood
  • 179
  • 3
  • 9
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.

diciu
  • 29,133
  • 4
  • 51
  • 68
0

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

stjohnroe
  • 3,168
  • 1
  • 27
  • 27