Hi All,

OS:AIX 5.3

I am using the following shell script for patch query but not getting the prompt to enter values for '&From_date' and '&TO_Date' in the patch query when called from the shell script as below:

patch_query.sh
============

#!/usr/bin/ksh

export DBALIST="xyz@xyz.com"
export USER=apps
export PWD=******
export SID=XYZ

sqlplus "$USER/$PWD" <<!
@patch_query.sql
exit
!
if [ `cat patch_query.alert|wc -l` -gt 0 ]
then
cat patch_query.alert -l patch_query.alert > patch_query.tmp
mailx -s "Patch Query Alert for ${SID}" $DBALIST < patch_query.tmp
fi

patch_query.sql
===============

set feed off
set linesize 100
set pagesize 200
spool patch_query.alert
select BUG_NUMBER "PATCH_NUMBER",to_char(creation_date,'DD-MON-YYYY HH24:MI:SS') CREATION_DATE from ad_bugs
where to_char(creation_date,'DD-MON-YYYY HH24:MI:SS') between '&From_date' and '&TO_Date';
spool off;

Error message:
==============


$ /home/oraxyz $ ./patch_query.sh

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Oct 7 08:16:25 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> Enter value for from_date: Enter value for to_date:
User requested Interrupt or EOF detected.
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
cat: 0652-050 Cannot open -l.
$ /home/oraxyz $

could anyone please let me know how to do it?

Thanks for your time!

Regards,

Let's do some debugging. Replace "sqlplus" with "cat >&2". Now run the script and redirect error to some file:

./patch_query.sh 2>debug.$$

Does it prompt now? Does debug.$$ contain the "right" output?