Home » SQL & PL/SQL » Client Tools » SQLPLUS is not coming out with the exit code if not connected
SQLPLUS is not coming out with the exit code if not connected [message #522049] Tue, 06 September 2011 02:09 Go to next message
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
`
Re: SQLPLUS is not coming out with the exit code if not connected [message #522052 is a reply to message #522049] Tue, 06 September 2011 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 59298
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle version and OS?

Regards
Michel

[Updated on: Tue, 06 September 2011 02:57]

Report message to a moderator

Re: SQLPLUS is not coming out with the exit code if not connected [message #522058 is a reply to message #522052] Tue, 06 September 2011 04:12 Go to previous messageGo to next message
hi_manoj
Messages: 7
Registered: August 2011
Location: INDIA
Junior Member
Orcale version -- Oracle Database 10g
OS Version -- SunOS
Re: SQLPLUS is not coming out with the exit code if not connected [message #522060 is a reply to message #522058] Tue, 06 September 2011 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59298
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First you will have to put the WHENEVER before connect.
Then as SPOOL is after CONNECT there will be nothing in the spool.

Regards
Michel

[Updated on: Tue, 06 September 2011 04:18]

Report message to a moderator

Re: SQLPLUS is not coming out with the exit code if not connected [message #522062 is a reply to message #522060] Tue, 06 September 2011 04:25 Go to previous messageGo to next message
hi_manoj
Messages: 7
Registered: August 2011
Location: INDIA
Junior Member
Hi Michel
I have tested the script with WHENEVER SQLERROR EXIT 1 before connect and after connect. In bot the case it is creating the spool file.

WHENEVER SQLERROR EXIT 1
CONNECT $Usr/$Pwd@$Server

and

CONNECT $Usr/$Pwd@$Server
WHENEVER SQLERROR EXIT 1
Re: SQLPLUS is not coming out with the exit code if not connected [message #522063 is a reply to message #522062] Tue, 06 September 2011 04:43 Go to previous message
Michel Cadot
Messages: 59298
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not my experience.

Regards
Michel
Previous Topic: format column problem
Next Topic: SQL developer(procedure)
Goto Forum:
  


Current Time: Thu Oct 02 12:27:37 CDT 2014

Total time taken to generate the page: 0.24913 seconds