Can't get shell parameters to pass properly to sqlplus

Shell Programming and Scripting
1

Gurus,

The issue I'm having is that my Shell won't accept SQL parameters properly......

Here's they way I'm running it....

applmgr@ga006hds [/home/users/applmgr/CW_Merge_Project]
=>  sh CW_MigrationDeployScript.sh apps <appspwd> <SID> '01-JAN' '31-MAR'

The process just hangs not submitting the SQL job...

#!/bin/ksh
export USAGE="USAGE: `basename $0` -e <DBUSER> <DBPASSWD> <TNSNAME> <FROM_DATE> <TO_DATE>"
if [ $# -lt 3 ]; then
echo ${USAGE}
exit 1;
fi
SCRIPTHOME=`pwd`
TMS_USER=$1
TMS_PWD=$2
TMS_DATABASE=$3
LL="${TMS_USER}/${TMS_PWD}@${TMS_DATABASE}"
FROM_DT=$4
TO_DT=$5
PARAMS="${FROM_DT} ${TO_DT}"
dow()                                                                   
{       
perl -e '
use POSIX qw(strftime);
@time=gmtime(time -(4*3600)); #=> GMT -4
$day = strftime("%A",0,0,0,$time[3],$time[4],$time[5],-1,-1,-1);
print "$day\n"'                                                            
}         
echo "$(dow `date "+%Y-%m-%d"` )"
if [[ "$(dow `date "+%Y-%m-%d"` )" = "Monday" ]]; then
YEAR="'2005'"
elif [[ "$(dow `date "+%Y-%m-%d"` )" = "Tuesday" ]]; then
YEAR="'2006'"
elif [[ "$(dow `date "+%Y-%m-%d"` )" = "Wednesday" ]]; then
YEAR="'2007'"
elif [[ "$(dow `date "+%Y-%m-%d"` )" = "Thursday" ]]; then
YEAR="'2008'"
elif [[ "$(dow `date "+%Y-%m-%d"` )" = "Friday" ]]; then
YEAR="'2009'"
elif [[ "$(dow `date "+%Y-%m-%d"` )" = "Saturday" ]]; then
YEAR="'2010'"
fi 
cd ${SCRIPTHOME}
sqlplus -s ${LL} @./RCV_SHIPMENT_HDR_LINE_PRE_OUTAGE.sql ${YEAR} ${PARAMS} > rcv_ship_hdr_line.log
echo ""
echo ""
echo ""
echo "Deployments Complete"

When I call the *.sql script manually, from Sql prompt from server, I get the expected results.

SQL> @RCV_SHIPMENT_HDR_LINE_PRE_OUTAGE.sql
Input truncated to 1 characters
Enter value for year: '2005'
old   2:   l_year varchar2(4):=&YEAR;
new   2:   l_year varchar2(4):='2005';
Enter value for start_day_month: '01-APR'
old   3:   l_start_day_month varchar2(10):=&START_DAY_MONTH;
new   3:   l_start_day_month varchar2(10):='01-APR';
Enter value for end_day_month: '30-JUN'
old   4:   l_end_day_month varchar2(10):=&END_DAY_MONTH;
new   4:   l_end_day_month varchar2(10):='30-JUN';
Updating RCV_SHIPMENT_HDR_LINE
PL/SQL procedure successfully completed.
SQL>

Anyone have any advice on how I can get the Shell to run the *.sql properly?

2

Hangs where?

3

One thing springs to mind. When you ran the SQL from within SQL*Plus (@RCV_SHIPMENT....), you wound up, at the end, with:

SQL>

(the SQL prompt, i.e. you're still inside SQL*Plus)

Perhaps you need to add an exit; to your SQL?

i.e.

$ cat SQL.sh

echo Hello
sqlplus -s scott/tiger @SQL.sql
echo Good bye

$ cat SQL.sql
select 1 from dual;

$ ./SQL.sh
Hello

	 1
----------
	 1

("hangs" - pressing Control-D, or typing exit would "fix" this!)

$ cat SQL.sql
select 1 from dual;
exit;

$ ./SQL.sh
Hello

	 1
----------
	 1

Good bye
1 Like
4

Yes, I remember the "exit" being an issue with this once before. I'll give it a try and let you know.