How to Export Data From an Oracle's Table to a Flat File on the Unix Box

Save

Oracle is a relational database that can be installed on the Unix operating system. You can output the contents of Oracle SQL statements to a flat file that is stored outside of the Oracle database. This file is a text file that contains the information from the table at the moment that it was created. It will not be updated or changed by the changes made to the Oracle database.

Things You'll Need

  • Unix operating system
  • Oracle database
  • Log into the Unix machine with your Oracle username.

  • Open a terminal window.

  • Type the command "sqlplus" to log into the Oracle database. You will be presented with the "SQL>" command prompt where you will type the following commands.

  • Type the command "SET SPACE 0" to set the space between columns in the text file.

  • Type the command "SET LINESIZE 75" to set the line size for the text file.

  • Type the command "SET PAGESIZE 0" to suppress all headings, page breaks, titles, the initial blank line and other formatting information in the text file.

  • Type the command "SET HEADING OFF" to suppress the printing of page headings in the text file.

  • Type the command "SET MARKUP HTML OFF SPOOL OFF" stop the printing of HTML markup information.

  • Type the command "SET COLSEP " "" to use a space (" ") to separate the columns in the text file.

  • Type the command "SPOOL output.txt" to send the output of the following statements to a text file named "output.txt."

  • Type the statement "SELECT * from table;" to select all of the data from the table. Replace the the word "table" with the actual name of the table.

  • Type the command "SPOOL off" to stop the writing of output to the text file.

  • Type the command "QUIT" to exit the Oracle database. You will be presented with the normal Unix command prompt where you will type the next command.

  • Type the command "cat output.txt" to view the contents of the text file.

References

Promoted By Zergnet

Comments

Resources

You May Also Like

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!