Mischiefblog
I make apps for other people

HOWTO Create a CSV report from an Oracle query

Posted by Chris Jones
On August 3rd, 2012 at 13:09

Permalink | Trackback | Links In |

Comments Off on HOWTO Create a CSV report from an Oracle query
Posted in General

Here’s a simple shell script that turns an Oracle query into a CSV file suitable for mailing.


SCRIPT_PATH=${HOME}/oracle/scripts
# capture stderr, stdout
export OUTFILE=$SCRIPT_PATH/my_report.`date +%m%d%y`
exec 1>$OUTFILE
exec 2>&1
 
# uncomment the following line to enable trace output
#set -x
 
# change this to where Oracle is installed
ORACLE_HOME=${HOME}/oracle/product/11.1.0/client_1
SQLPLUS=$ORACLE_HOME/sqlplus"
 
# I assume you're hiding database credentials in a store rather than in plain text
# (but since you probably don't, you'll need to put these in plaintext)
AUTH_STORE="/opt/authstore/auth"
DB_KEY="dbkey"
USERNAME=`$AUTH_STORE $DB_KEY username`
PASSWORD=`$AUTH_STORE $DB_KEY password`
ORACLE_SID=`$AUTH_STORE $DB_KEY sid`
 
MAIL="/usr/bin/env mailx"
RUNTIME=`date +%Y%m%d%H`
REPORT_FILENAME="my_report.$RUNTIME.csv"
 
EMAIL_ADDRESS="foo@bar.com"
 
# change to the directory where the report will go
cd $SCRIPT_PATH
 
# get the report
$SQLPLUS -S $USERNAME/$PASSWORD@$ORACLE_SID @report_query.sql
 
# reformat the report to a CSV
sed '$d' $SCRIPT_PATH/my_report.log | awk '{print $1, $2 "," $3 "," $4 "," $5 "," $6 "," $7 "," $8 "," $9;}' > $REPORT_FILENAME
echo "CREATE_DATE,FIRST_NAME,MIDDLE_NAME,LAST_NAME,ADDRESS_1,CITY,STATE,ZIP" | cat - $REPORT_FILENAME > $SCRIPT_PATH/$REPORT_FILENAME.tmp
mv $SCRIPT_PATH/$REPORT_FILENAME.tmp $REPORT_FILENAME
 
# mail the CSV file
$MAIL -s "My report $RUNTIME" $EMAIL_ADDRESS < $REPORT_FILENAME echo "`date` Mailed My report $RUNTIME to $EMAIL_ADDRESS"

You'll still need report_query.sql to extract your report from Oracle.


set LINESIZE 255
set PAGESIZE 0
col last_name for a20
col creation_date for a25
col zip for a5
col address_1 for a25
spool /home/username/oracle/scripts/my_report.log
SELECT creation_date,
        creation_time,
        first_name,
        middle_name,
        last_name,
        address_1,
        city,
        state,
        zip
FROM
        user_accounts
WHERE
        create_date > SYSDATE - 7
ORDER BY creation_date, creation_time;
spool off
exit;

Comments are closed.