Laurent Schneider

How to send commands to sqlplus ?
Use stdin

$ ( echo prompt $(hostname) $(date); echo desc emp ) | sqlplus -s scott/tiger
dbsrv85a Mon Jun 6 17:01:46 CEST 2005
Name Null? Typ
—- —– —
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

How do you retrieve data from your database with the shell and sqlplus ?

USERS=$(echo “select ‘@’,username from dba_users;” | # this is my stdin
sqlplus -s “/ as sysdba” | # sqlplus as a filter
awk ‘/^ORA-/ /^SP2-/ { print|”cat >&2″;exit 1 }
/^@/ { print $2 }’ # search for @
)
if [ $? != 0 ] # awk returned error
then
echo error >&2
# exit 1
fi
for u in $USERS # do something
do
echo hello $u
done

hello SYSTEM
hello SYS
hello PERFSTAT
hello SCOTT
hello DIP
hello U01
hello EXFSYS
hello MON
hello DBSNMP
hello OUTLN

How do I get OS output from sqlplus ?
The high end solution is to use Java. However, this implies you have Java in the Database (cost memory), you have the necessary privileges (which can be easily misused to destroy your system), and you use plsql. Have a look at my blog about Disk Free space.
Ok, let’s imagine I do not want to install all that stuff.

SQL> host uptime
17:09pm up 84 days, 23:32, 14 users, load average: 1.13, 1.23, 1.28

How do I get the return code?
SQL> prompt return code is &_RC
return code is 0

But this does not work in 10g

How do I use the os user, hostname, system date, ip address?
Use SQL!

SQL> select sys_context(‘USERENV’,'OS_USER’), host_name, sysdate, UTL_INADDR.GET_HOST_ADDRESS(host_name) from v$instance;

oracle dbsrv85a 06.06.2005 17:14:46 147.50.59.167

Ok, I want to know the OS and store it in a variable!
Hmm, hmm… I like to do those kind of things by sourcing temp files
SQL> def os=”"
SQL> host echo def os=”$(uname -s)” > /tmp/tmpos.sql
SQL> start /tmp/tmpos.sql
SQL> select ‘&os’ from dual;
AIX