Skip to main content

Connect to an Oracle Database and run a query from a Bash script

Published: 2019-06-07 • Last updated: 2019-09-12

# Prerequisites

  • sqlplus (SQL*Plus) 12.2.x or higher is installed and on your PATH
  • The following files are in the same directory

# Files

A query.sql file:

-- this file must end in a new line
SELECT 'foo'
FROM dual
WHERE 1 = 1
SELECT 'bar'
FROM dual
WHERE 2 = 2;

An .env file:

export ORACLE_HOST=""
export ORACLE_PORT="1521"
export ORACLE_DATABASE="some_service"
export ORACLE_USERNAME="some_user"
export ORACLE_PASSWORD="some_password"

A script.bash file:


# Load database connection info
source .env 

# Read query into a variable
sql="$(cat query.sql)"

# If sqlplus is not installed, then exit
if ! command -v sqlplus > /dev/null; then 
  echo "SQL*Plus is required..."
  exit 1 

# Connect to the database, run the query, then disconnect
echo -e "SET PAGESIZE 0\n SET FEEDBACK OFF\n $sql" | \


$ ./script.bash

$ ./script.bash > results.txt


  • The SET PAGESIZE 0 option suppresses all headings, page breaks, titles, the initial blank line, and other formatting information
  • The SET FEEDBACK OFF option suppresses the number of records returned by a script
  • The -S option sets silent mode which suppresses the display of the SQL*Plus banner, prompts, and echoing of commands