shell + sqlplus

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

10 thoughts on “shell + sqlplus

  1. Anonymous

    Great skill, let me know that could transfer shell variable to sqlplus . Thanks~

  2. Danyc

    Laurent Schneider :sqlplus <<EOFprompt $MYVARconnect / as sysdbaselect ā€˜$PWDā€™ from dual;quitEOF
    ok?

    Hi Laurent,

    First please let me THANK YOU for sharing your knowledge šŸ™‚
    Do you have any idea why do i get the result like

    sqlplus -s / <<EOF
    prompt $t
    select `$PWD` from dual;
    quit
    EOF
    -bash: /home/oracle: is a directory
    /home/oracle
    select from dual
    *
    ERROR at line 1:
    ORA-00936: missing expression

    If i’m using dbms_system.get_env everything work ok.

    Many thanks,
    Dani

  3. Laurent Schneider Post author

    @Probal
    doing fine? not sure, not even in 11.2.0.3


    $ sqlplus /nolog

    SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 18 10:18:52 2011

    Copyright (c) 1982, 2011, Oracle. All rights reserved.

    SQL> host false

    SQL> prompt &_RC
    1
    SQL> host true

    SQL> prompt &_RC
    0
    SQL> host false

    SQL> prompt &_RC
    1
    SQL> conn / as sysdba
    Connected.
    SQL> host true

    SQL> prompt &_RC
    0
    SQL> host false

    SQL> prompt &_RC
    0

    still very buggy, test above on 11.2.0.3 / Solaris.

    On which OS/Version did you try?

  4. Anonymous

    @Laurent Schneider
    Hi,

    I too have similar kind of requirement. I am using 11g oracle database. &_RC is not working.
    please provide me any other alternate solution.
    Here i am moving the file from one directory to another directory. if move command fails i want capture the $? value and pass it to sql.

    /* Move the Vendor File to Server */
    HOST mv &lUtlPath/&lFileName &lVendorFilePath; if [ $? -ne 0 ]; then echo “ERROR: Failed to copy or move the Vendor File” exit 1; fi
    DECLARE
    l_ret_code VARCHAR2(100):=&_RC;
    BEGIN
    IF l_ret_code 0 THEN
    RAISE_APPLICATION_ERROR(-20001,’Failed to copy or move the Vendor File’||l_ret_code);
    END IF;
    END;
    /

    @Laurent Schneider

Comments are closed.