How do I create a flat file with values fetched from a database in UNIX?

Question

How do I create a flat file with values fetched from a database ( using select ) in UNIX?

Answer 1

You should be able to call sqlplus from within a shell script, and pipe the results to a flat file. See this answer for details.

Answer 2

For variables passed to a shell script try a here document:

#!/bin/ksh
var=$(printf "'%s'" `date +%b-%d-%Y`)
sqlplus -s me/mtpasswd@mydbname <<! 
   set pages 55
   spool outfile.lis
   select * from mytable where sales_date= $var ;
   spool off
!

This uses todays date. The flat file is outfile.lis

Answer 3

If you're using SQLPlus, use the SPOOL directive to output script output to a file. This SO question details how to get a CSV file via SQLPlus/SPOOL, for example.

SPOOL your_file.txt

    SELECT 1, 'test' 
      FROM DUAL

SPOOL OFF

Related:

• Oracle/PLSQL: Execute an SQL script file in SQLPlus