passing parameters from a shell script to sqlplus | Unix Linux Forums | Shell Programming and Scripting

  Go Back    


Shell Programming and Scripting Post questions about KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and OTHER shell scripts and shell scripting languages here.

passing parameters from a shell script to sqlplus

Shell Programming and Scripting


Closed Thread    
 
Thread Tools Search this Thread Display Modes
    #1  
Old 03-09-2005
phani phani is offline
Registered User
 
Join Date: Mar 2005
Last Activity: 6 April 2005, 6:11 AM EDT
Posts: 12
Thanks: 0
Thanked 1 Time in 1 Post
passing parameters from a shell script to sqlplus

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 :-)
The Following User Says Thank You to phani For This Useful Post:
unisunny (04-13-2011)
Sponsored Links
    #2  
Old 03-09-2005
tmarikle tmarikle is offline Forum Advisor  
Registered User
 
Join Date: Jan 2005
Last Activity: 27 September 2011, 12:36 PM EDT
Posts: 683
Thanks: 0
Thanked 4 Times in 4 Posts
Quote:
Originally Posted by phani
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
Looks like your problem is with invoking your script.


Code:
for i in Bangalore Niskayuna Shanghai Irvine Munich
do
    sqlplus -s hpsddw/hpsddw @${HPSD_HOME}/sql/site_extract.sql $i<<EOF
    prompt SQLPlus says: $i
EOF
done

Thomas
Sponsored Links
    #3  
Old 03-13-2005
andyl andyl is offline
Registered User
 
Join Date: Mar 2005
Last Activity: 27 October 2008, 1:54 AM EDT
Location: Victoria, Australia
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
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.' )

Last edited by andyl; 03-13-2005 at 08:55 PM..
Sponsored Links
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
specified path name is too long passing parameters to awk via shell script script_op2a Shell Programming and Scripting 0 11-09-2010 07:03 PM
Passing parameters(value object) to shell script. Neha Goyal Shell Programming and Scripting 3 05-12-2010 04:11 AM
Shell Script Passing Parameters For Directory Listing wtolentino Shell Programming and Scripting 9 07-06-2009 05:53 PM
error in passing a variable to sqlplus from a shell script kripssmart Shell Programming and Scripting 3 07-08-2008 08:01 PM
help me in sending parameters from sqlplus script to unix shell script Hara Shell Programming and Scripting 2 01-29-2008 03:31 PM



All times are GMT -4. The time now is 12:46 PM.