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

Looks like your problem is with invoking your script.

Thomas

First time i've replied. Normally loiter.

Phani:
This worked for me when I was doing something similar. Used '.' to terminate variable names.

Hope this helps

Andrew

define Org_Unit = "&1"
define Year_Code = "&2"
define Semesters = "&3"
define Unit_Cd = "&4"
define Unit_Location_Cd = "&5"
define Unit_Class = "&6"
define fileroot = "Q:\ELUP\20030318\"

host "IF NOT exist &fileroot.&Org_Unit. mkdir &fileroot.&Org_Unit."
prompt &fileroot.&Org_Unit.\&Year_Code._&Unit_Cd._&Semesters._&Unit_Location_Cd.&Unit_Class..csv
spool &fileroot.&Org_Unit.\&Year_Code._&Unit_Cd._&Semesters._&Unit_Location_Cd.&Unit_Class..csv

Same with variable values
( CAL_INSTANCE1.CAL_TYPE = 'ACAD-YR' AND CAL_INSTANCE1.ALTERNATE_CODE = '&Year_Code.' )