Return code from sqlplus session in Shell script

if you execute a sqlplus block inside a shell script and you need to perform different actions based on its return code. However no metter wheather the sqlplus session terminates with success or failures, its return code always equals 0 which means a successful result. It can be demostrated as:

${ORACLE_HOME}/bin/sqlplus "/ as sysdba" >> ${LogFile} << EOF
startup mount restrict;
exit;
EOF
rc=$?
echo returncode=$rc

the output:

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Sep 11 11:32:41 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected to an idle instance.
SQL> ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '/u01/app/oracle/admin/STDBY10G/pfile/spfileSTDBY10G.ora'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> Disconnected

returncode=0

Solution

“whenever sqlerror exit sql.sqlcode” can be used to terminate a sqlplus session and return an error code, similarly, “whenever oserror exit oscode” returns OS error code. These options are valid since Oracle 9i.
An example:

${ORACLE_HOME}/bin/sqlplus "/ as sysdba" >> ${LogFile} << EOF
whenever oserror exit oscode
whenever sqlerror exit sql.sqlcode
startup mount restrict;
exit;
EOF
echo returncode=$?

the output:

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Sep 11 11:43:57 2012

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

Connected to an idle instance.

SQL> SQL> ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '/u01/app/oracle/admin/STDBY10G/pfile/spfileSTDBY10G.ora'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Disconnected
Returncode=54

Unix permissions problem under ORACLE_HOME

 

Apparently this is a known Oracle bug, after installing Oracle 10.2.0 Unix permissions under
ORACLE_HOME are not set correctly, so directories and files are accessible to the installation
user and group (i.e. oracle:dba) but not to the reset of the world (others). If you get any of
the following errors the problem may be caused by this permissions bug.

$ sqlplus /
ld.so.1: sqlplus: fatal: /apps/oracle/product/10.2.0/db_1/lib/libclntsh.so.10.1: Permission
denied

Did you set the permissions on the whole directory or just that one library?

$ sqlplus /
Error 6 initializing SQL*Plus
Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

$ sqlplus /
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly

One way to fix this is to run:

$ chmod -R o=g $ORACLE_HOME

However, make sure this doesn not violate your security policy.

Set sqlplus prompt for standby database

I use Tom Kyte’s login.sql script to set up a nice prompt in sqlplus like “username@db_name> ”

column global_name new_value gname
select lower(user) || '@' ||
lower(substr( global_name, 1, decode( dot,
0, length(global_name),
dot-1) )) global_name
from (select global_name, instr(global_name,'.') dot
from global_name );
set sqlprompt '&gname> '

However it doesn’t distinguish primary database from standby databases since they all share a same global_name.

DB_NAME: orcl

GLOBAL_NAME: orcl.domainname

Primary database db_unique_name: roma

Standby database db_unique_name: milano

I use the script below to extract DB_UNIQUE_NAME from USERENV namespace of a session.

column value new_value newsqlprompt
with a as
(select sys_context('USERENV', 'DB_UNIQUE_NAME') db_uniq_name from dual)
select lower(user || '@' || db_uniq_name) value from a;

set sqlprompt '&newsqlprompt> '

Now it shows a different sqlplus prompt sign for primary database than the one for standby database.

sqlplus guang/iamguang
guang@milano>

sqlplus guang/iamguang
guang@roma>

Output data to Excel using Sqlplus

1) Set long linesize and big pagesize to avoid wordwrapping and heading in every a few lines

SQL> set lines 1000 pages 50000

2) Set makeup html

SQL> set markup html on spool on

3) Spool an Excel file

SQL> spool output.xls

query goes here…..

4) Close the spool file

SQL> spool off