Passing variables between Linux/Unix shell SQL statements

The flexible combination of Linux/Unix Shell and SQL variable transmission greatly improves DBA efficiency, this article provides a few simple examples of passing variables between Linux/Unix shell SQL statements for your reference.

Call SQL in Linux/Unix. For details about RAMN, refer to SQL and RMAN scripts in Linux/Unix shell scripts.

I. Example

1. method 1 for accepting SQL return values for shell Variables
Oracle @ SZDB: ~> More./retval. sh
#! /Bin/bash
RETVAL = 'sqlplus-silent scott/tiger <EOF
Set pagesize 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SELECT * FROM emp WHERE ename = 'Scott ';
EXIT;
EOF'
If [-z "$ RETVAL"]; then
Echo "No rows returned from database"
Exit 0
Else
Echo $ RETVAL
Fi

Oracle @ SZDB: ~> Chmod u + x retval. sh
Oracle @ SZDB: ~> ./Retval. sh
7788 scott analyst 7566 19-APR-87 34171.88 20

2. method 2 for shell variables to accept SQL return values
Oracle @ SZDB: ~> More./retval_2.sh
#! /Bin/bash
Sqlplus-S "scott/tiger" <EOF
Set pagesize 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
Col cnt new_value v_cnt
SELECT count (*) cnt FROM emp WHERE deptno = 10;
Exit v_cnt
EOF
VALUE = "$? "
Echo "show rows for deptno 10: $ VALUE"

3. Pass the shell variable as a parameter to the SQL script
Oracle @ SZDB: ~> More./retval_3.sh
#! /Bin/bash
V_empno = "$1"
Sqlplus-S "scott/tiger" <EOF
Set pagesize 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SELECT ename FROM emp WHERE empno = $ v_empno;
Exit
EOF
Exit

Oracle @ SZDB: ~> ./Retval_3.sh 7788.
SCOTT