------------------------------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