Extract Data From DB With File Count As Filename_Count Of Rows

Need Database From Database In CSV Format And CSV Filename Should Be Like "CSV_RowCount.csv"


1. Create Report.sql File In /home/oracle/ "/home/oracle/Report.sql"

Content Of Report.sql File:


Cat /home/oracle/Report.sql

spool /tmp/data/data_exp.csv
set echo       off
set feedback   off
set heading    off
set linesize   32767
set pagesize   10000
set termout    off
set trimspool  on
set verify     off
set colsep "|"
---------<Place Query>-------------
spool off

2. Create Extract_Data.sh File In /home/oracle "/home/oracle/Extract_Data.sh"

Content Of Extract_Data.sh File:

Cat /home/oracle/Extract_Data.sh

#!/bin/ksh
sqlplus -s <<!
/ as sysdba
spool /home/oracle/Data_Extract.log
/home/oracle/Report.sql
spool off
today=`date '+%Y%m%d'`;
value=$(wc -l </tmp/data/data_exp.csv |awk '{print $1}')
echo "Total number of Rows :$value"
#touch "$today"_"$value.txt"
mv /tmp/data/data_exp.csv /tmp/testdbfs/"$today"_"$value.pip"



3. Schedule Cronjob 

Crontab -l

05 00 * * * /home/oracle/Extract_Data.sh


Note:

Modify column Size , Pages Size As Per Query