Getting output in a flat file using oracle on UNIX

Question

How to get query output to a flat file using Oracle on UNIX? For example: I have a TEST table; I want to get the contents of the TEST table into a flat file, and then save the output in another folder in .txt format.

Answer 1

See Creating a flat file in the SQL * Plus User Guide and Reference.

Answer 2

in the SQLplus oracle terminal you can enter a coil; run the spool off request;

Now it will contain the query results.

In fact, it will contain all the output to the terminal from the moment the coil command is executed until the coil is turned off.

Answer 3

If you have access to directories on the database server and the authority to create Directory objects in Oracle, you have many options.

For example, you can use the UTL_FILE package (part of the built-in PL / SQL modules) to read or write files at the operating system level.

Or use the "external table" function to define objects that look like separate tables in Oracle but are actually flat files at the OS level. Well documented in Oracle docs.

In addition, for one-time tasks, most SQL and PL / SQL tools provide the ability to move data to and from the database. In a Windows environment, Toad is good at this. Thus, Oracle is a free SQLDeveloper that runs on many platforms. You would not want to use them for a process that starts every day, but they are suitable for single moves. I usually found them easier to use than SQL * Plus buffering, but this is a primitive version of the same functionality.