Hi ,
I want to pass parameters from a shell script to a sql script and use the parameter in the sql query ..and then I want to spool a particular select query on to my unix box... for 4 different locations by writing only one sql script
Right now no file is generated on the unix box...it is a solaris box uing korn shell
this is my shell script for reference....
TWO_TASK=$1;export TWO_TASK
TNS_ADMIN=/home/chandrap;export TNS_ADMIN
ORACLE_BASE=/s40/app/oracle;export ORACLE_BASE
ORACLE_HOME=${ORACLE_BASE}/product/9.2.0.3;export ORACLE_HOME
PATH="$ORACLE_HOME/bin:/usr/ccs/bin:/usr/sbin:$PATH";export PATH
date_in_mmddyyyy="`date +\"%m%d%Y%H%M%S\"`"
HPSD_HOME=/home/chandrap/dwh
LOG_DIR=${HPSD_HOME}/logs
DWH_LOG=$LOG_DIR/global_extract${date_in_mmddyyyy}.log
echo $date_in_mmddyyyy > $DWH_LOG
echo ${ORACLE_HOME} >> $DWH_LOG
echo ${TWO_TASK} >> $DWH_LOG
if [ "${TWO_TASK} " = " " ]; then
echo "PARAMETER: Source or Target ORACLE_SID not set; exiting application"
exit
fi
for sitename in Bangalore Niskayuna Shanghai Irvine Munich
do
sqlplus -s hpsddw/hpsddw@$TWO_TASK ${HPSD_HOME}/sql/site_extract.sql $sitename >> $DWH_LOG
echo $sitename
done
grep 'ORA-' $DWH_LOG
if [ $? -eq 0 ]
then
mailx -s "Error in Global extract " $maillist < $DWH_LOG
exit 1
else
mailx -s "Global extract Successful" $maillist < $DWH_LOG
exit 0
fi
and this is my sql script...
spool /projects/hpsd_dwh/global_extract/'&&site_name'||'.csv'
define site_name = &&1
SELECT
'"'||CASE_ID||'","'||
CUSTOMER_SSO_ID||'","'||
CUSTOMER_NAME ||'","'||
CUSTOMER_ORGANIZATION||'","'||
CUSTOMER_SITENAME||'","'||
ENTERED_BY_PERSON||'","'||
ENTERED_BY_PERSON_ORG||'","'||
ASSIGNED_TO_PERSON||'","'||
ASSIGNED_TO_WORKGROUP ||'","'||
ASSIGNED_TO_ORGANIZATION||'","'||
DISPATCH_TO_WORKGROUP||'","'||
ESCALATE_TO_TIER||'","'||
CASE_CATEGORY_PARENT||'","'||
CASE_CATEGORY||'","'||
CLASSIFICATION_PARENT1||'","'||
CLASSIFICATION_PARENT2||'","'||
CLASSIFICATION_PARENT3||'","'||
CLASSIFICATION_PARENT4||'","'||
CLASSIFICATION_PARENT5||'","'||
CLASSIFICATION_PARENT6||'","'||
CLASSIFICATION_PARENT7||'","'||
CLASSIFICATION_PARENT8||'","'||
CONFIG_ITEM_SEARCH_CODE||'","'||
CONFIG_ITEM_CATEGORY||'","'||
CONFIG_ITEM_SUPPLIER||'","'||
TITLE||'","'||
IMPACT||'","'||
STATUS||'","'||
OS||'","'||
MEDIUM ||'","'||
CLOSURE_CODE ||'","'||
IVOC_OFT_NAME||'","'||
DEADLINE||'","'||
ACTUAL_START||'","'||
ACTUAL_FINISH||'","'||
T1_RESPONSETIME||'","'||
T1_T2_ESCALATION||'","'||
T1_HOLDTIME||'","'||
T2_RESP_TIME||'","'||
WAITING_DURATION||'","'||
VENDOR_RESPONSETIME||'","'||
VENDOR_RESOLUTIONTIME||'","'||
ACTUAL_DURATION||'","'||
RE_OPEN_DURATION||'","'||
INBOUND_PHONE_CALLS ||'","'||
STATUS_CALL_BACK ||'","'||
T1_ASSIGNMENT ||'","'||
OUTBOUND_PHONE_CALLS||'","'||
RAL1_POSSIBILITY||'","'||
WAITING_STATUS||'","'||
BUSINESS_UNIT||'","'||
RESOLUTION_TIME||'","'||
RESOLUTION_TIME_VENDOR||'","'||
OVERALL_RAL1_MET||'","'||
SLA_MET||'","'||
SLA_MET_VENDOR||'","'||
ACTUAL_DURATION_SLA||'"'
FROM
HPSDDW_MTD_CASE_DETAILS
where customer_sitename = '&&site_name';
spool off;
exit;
pls get back to me asap...as it is very urgent....
thnks a lot in advance 