Linux/Unix: Execute Oracle Commands from Shell Script

Linux/Unix: Execute Oracle Commands from Shell Script

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • Linux
  • 2 mins read

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: