In this tutorial, you will learn how to execute Oracle commands from the shell script in Linux/Unix systems.
Syntax
To execute Oracle commands from a shell script use the following syntax:
sqlplus -silent username/psw@dbname <<QUERY_NAME
--Oracle Database Commands
QUERY_NAME
Where the QUERY_NAME could be any valid name and in the midsection of the SQL*PLUS block, you can execute any DDL or DML Oracle Statements. The following are the examples:
1. Execute Select Statement in Shell Script
#!/bin/bash
sqlplus -silent scott/tiger@orcl <<SQL_QUERY
select empno, ename, sal from emp
where deptno = 30;
SQL_QUERY
Output
EMPNO ENAME SAL
---------- ---------- ----------
7654 MARTIN 11641.56
7698 BLAKE 26542.7
7844 TURNER 13969.85
7900 JAMES 8847.64
2. Access a Variable Declared in Shell Script in SELECT Statement
#!/bin/bash
n_deptno=30
sqlplus -silent scott/tiger@orcl <<SQL_QUERY
select empno, ename, sal from emp
where deptno = $n_deptno;
SQL_QUERY
The output would be the same as for the first example.
3. Getting the Output of SQL Command into a Text/Log File
Add >>filename.txt after the <<SQL_QUERY. Below is the example:
#!/bin/bash
n_deptno=30
sqlplus -silent scott/tiger@orcl <<SQL_QUERY>>output.log
select empno, ename, sal from emp
where deptno = $n_deptno;
SQL_QUERY
Now you can check the result of SQL command in the output.log file. For example:
cat output.log
4. Check if Oracle Commands in SQL*PLUS Executed Successfully or Failed
In the following shell script, it will take SQL*PLUS block execution result using the ($?) command into a variable named cmd_success. If the value is 0, then it means it is success else failed.
#!/bin/bash
n_deptno=30
sqlplus -silent scott/tiger@orcl <<SQL_QUERY>>output.log
select empno, ename, sal from emp
where deptno = $n_deptno;
SQL_QUERY
cmd_success=$?
if [ $cmd_success -eq 0 ]
then
echo "Oracle Commands executed successfully."
else
echo "Oracle Commands execution failed."
fi
Output
Oracle Commands executed successfully.
5. Take the SQL Query Result into a Variable
#!/bin/bash
empname=`sqlplus -silent scott/tiger@orcl <<SQL_QUERY
set pagesize 0 feedback off verify off heading off echo off
select ename from emp
where empno = 7654;
SQL_QUERY`
echo $empname
See also:
- How to Call Oracle Stored Procedure from Shell Script?
- Reading a Password from a File in Linux
- Linux Dialog Examples