How to obtain the SELECT result value in a Shell script

How to obtain the SELECT result value in a Shell script

Sometimes we may need to execute the SELECT statement in the Shell script and assign the result to a variable. In this case, we can use the following methods to achieve the goal.

#! /Bin/ksh
#
# Created: 2015.05.25
# Updated: 2015.05.25
# Author: Jet Chenxi Zhang
# Description: Get SELECT result in Shell

# Variable Definitions #
Oracle_SID = audtest
ORACLE_HOME =/app/oracle/product/database/11.2.0/db_1
PATH = $ PATH: $ ORACLE_HOME/bin
DBNAME =
 
# Get Database name by quering v $ database #
DBNMAE ='
Sqlplus-s/nolog <EOF
Set echo off feedback off heading off underline off;
Conn/as sysdba;
Select name from v \ $ database;
Exit;
EOF'
 
Echo "Database name:" $ DBNMAE

The above code gets the Database Name. The execution result is as follows:

[Oracle @ hxddcx02 ~] $./Query_dbname.sh
Database name: AUDTEST

You can see that the SELECT result has been correctly assigned to the Shell variable. This method can also be used if the SELECT statement contains values of multiple columns. You only need to split the result, for example, awk:

#! /Bin/ksh
#
# Created: 2015.05.25
# Updated: 2015.05.25
# Author: Jet Chenxi Zhang
# Description: Get SELECT results in Shell

# Variable Definitions #
ORACLE_SID = audtest
ORACLE_HOME =/app/oracle/product/database/11.2.0/db_1
PATH = $ PATH: $ ORACLE_HOME/bin
QUERYRES =
DBID =
DBNAME =
 
# Get Database name by quering v $ database #
QUERYRES ='
Sqlplus-s/nolog <EOF
Set echo off feedback off heading off underline off;
Conn/as sysdba;
Select dbid, name from v \ $ database;
Exit;
EOF'
 
DBID = 'echo $ QUERYRES | awk-f'' {print $1 }''
DBNAME = 'echo $ QUERYRES | awk-f'' {print $2 }''
Echo "Database ID:" $ DBID
Echo "Database name:" $ DBNAME

The running result is as follows:

[Oracle @ hxddcx02 ~] $./Query_dbname2.sh
Database ID: 811711272
Database name: AUDTEST

This article permanently updates the link address: