How to get the output of a query into a flat file using Oracle on UNIX? For example: I have a TEST table; I want to get the content of the TEST table into a flat file and then store the output in some other folder in .txt format.
+1
A:
in the oracle SQLplus terminal you could type spool ; run your query spool off;
Now the would contain the results of the query.
In fact it would contain all the output to the terminal since the execution of the spool command till spool off.
Sathya
2009-06-05 15:58:02
+3
A:
See Creating a Flat File in the SQL*Plus User's Guide and Reference.
markusk
2009-06-05 16:13:05
+1 (I might add that LINESIZE may need to be set to a larger value to avoid line wrap, with TRIMSPOOL on trailing blanks will be removed from each line, i sometimes use column formatting directives)
spencer7593
2009-06-05 16:48:40
A:
If you have access to directories on the database server, and authority to create "Directory" objects in Oracle, then you have lots of options.
For example, you can use the UTL_FILE package (part of the PL/SQL built-ins) to read or write files at the operating system level.
Or use the "external table" functionality to define objects that look like single tables to Oracle but are actually flat files at the OS level. Well documented in the Oracle docs.
Also, for one-time tasks, most of the tools for working SQL and PL/SQL provide facilities for moving data to and from the database. In the Windows environment, Toad's good at that. So is Oracle's free SQLDeveloper, which runs on many platforms. You wouldn't want to use those for a process that runs every day, but they're fine for single moves. I've generally found these easier to use than SQL*Plus spooling, but that's a primitive version of the same functionality.
Jim Hudson
2009-06-05 20:11:00