Thursday, January 20, 2011

Script that Generates Report from Oracle DB

------------------------------Script---------------------------------

#!/bin/sh

ORACLE_HOME=/opt/oracle
export ORACLE_HOME
ORACLE_BASE=/opt/oracle
export ORACLE_BASE
ORACLE_OWNER=oracle
export ORACLE_OWNER
ORACLE_SID=DB_NAME
export ORACLE_SID
NLS_LANG="american_america.we8iso8859p1"
export NLS_LANG

PATH=/usr/bin:/bin:/usr/local/bin:$ORACLE_HOME/bin
export PATH

outfile="/export/home/`date +"%Y%m%d"`.txt"

sqlplus -s username/password@DB_Name @$HOME/gen_report.sql > $outfile

----------------------------SQL File--------------------------------

select USER_ID as A, B, to_char(TIMESTAMP,'hh:mi:ssam') as Time From Table WHERE to_char(TIMESTAMP,'DD-Mon-YY') = (select to_char(sysdate, 'DD-Mon-YY') from dual) AND CONDITION_A = 'true' order by timestamp;

whenever sqlerror exit 99
whenever oserror exit 88
set pages 1000 echo off verify off feedback off

alter session set nls_date_format='YYMMDD HH24:MI:SS';
set line 200
col user_id format a15
col remarks format a50

select USER_ID as A, B, to_char(TIMESTAMP,'DD-Mon-YY hh:mi:ssam') as Time From username.table_name WHERE to_char(TIMESTAMP,'DD-Mon-YY') = (select to_char(sysdate-1, 'DD-Mon-YY') from dual) AND Condition_A = 'true' order by timestamp;

exit


No comments:

Post a Comment