Hi Frndz,
I have req in which i need to run the query stored in some file and then store the resultset of the query in some other file.
PFB the code i have written:
#----Start job log---
SQLLOG='/opt/app/vertica2/cdr_dev/logs/conn_orac_db.log'
ORA_USER='abc'
ORA_PASSWD='xyz'
ORA_CONN='pqr'
ORA_QUERY='qww.sql'
echo "Connecting to Oracle: `date +'%Y-%m-%d %H:%M:%S'`" > $SQLLOG
echo "SQL Script Name:$ORA_QUERY " >> $SQLLOG
echo " " >> $SQLLOG
cd $ORACLE_HOME
sqlplus -s /nolog << EOF >> $SQLLOG 2>&1
conn $ORA_USER/$ORA_PASSWD@$ORA_CONN
set colsep '^]'
set echo off
set feedback off
set pagesize 0
set wrap off
spool $ORA_RESULT
@$ORA_QUERY
disconnect
EOF
RC="$?"
echo "Return code: $RC" >> $SQLLOG
echo " " >> $SQLLOG
echo "Connection end: `date +'%Y-%m-%d %H:%M:%S'`" >> $SQLLOG
echo " " >> $SQLLOG
Now i am able to genrate the file with required resultset...the only prob is.i am getting data in below format:
rows will be truncated
rows will be truncated
rows will be truncated
rows will be truncated
rows will be truncated
rows will be truncated
rows will be truncated
rows will be truncated
rows will be truncated
SGSWKJJ6IXFH
SGSWNLDMW8VY
SGSW99N855JE
SGSWJG5K2WVJ
My actual data is (in oracle)
SGSWKJJ6IXFH|8/25/2012 2:31:20 AM|%|Single Use|||3rd Party|0011917||5% off $50 storewide products_9_1_12||9/1/2012 7:00:00 AM|12/1/2012 6:59:00 AM|8/25/2012 2:31:20 AM|8/25/2012 2:31:20 AM
Note:i used symbol "|" jus to differentiate my data.
and my expected data should be:
SGSWKJJ6IXFH^]8/25/2012 2:31:20 AM^]%^]Single Use^]^]^]3rd Party^]0011917^]^]5% off $50 storewide products_9_1_12^]^]9/1/2012 7:00:00 AM^]12/1/2012 6:59:00 AM^]8/25/2012 2:31:20 AM^]8/25/2012 2:31:20 AM
Please give ur inputs frndz..need it urgently>
TIA