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
Great skill, let me know that could transfer shell variable to sqlplus . Thanks~
sqlplus <<EOF
prompt $MYVAR
connect / as sysdba
select ‘$PWD’ from dual;
quit
EOF
ok?
Creating a bind variable and assigning the OS value using HOST is cool!! -:)
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
maybe try with single-quote instead of back-quotes
select '$PWD' from dual;Superb, just to add one thing……
prompt &_RC is doing just fine in 10g also.
@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?
I have created a new support request for bug 3798918
@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
Thanks for the code, really skillful.