| SQLPLUS is not coming out with the exit code if not connected [message #522049] |
Tue, 06 September 2011 02:09  |
 |
hi_manoj
Messages: 7
Registered: August 2011
Location: INDIA
|
Junior Member |
|
|
Hi
I have a script which connects to Oracle and return 2 date value. few days back the database was down and when the script execute and return same garbage value in the spool file.
I added a exception handler WHENEVER SQLERROR EXIT 1 befor the connection start and after connection start. But in both the case it is also writing to the spool file.
I would like to exit the connection with a value <> 0 if any connection issue happens like (user id, password, database down error type of issue.)
No information should go to the spool file
Output=`$ORACLE_HOME/bin/sqlplus -S /nolog << EOF
CONNECT $Usr/$Pwd@$Server
WHENEVER SQLERROR EXIT 1
spool ${ROOT_DIR}/.delta_date.tmp
set verify off
select to_char(max(bct_delta_rg_end_dt) + ${CYCLE_VALUE},'YYYY-MM-DD'), to_char(max(bct_delta_rg_end_dt),'YYYY-MM-DD') from etl_batch_mnt
where schedule_name ='COB';
spool off
EXIT
EOF
`
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
