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. How can shell variables accept SQL return values? Oracle @ szdb: ~> More./retval. Sh #! /Bin/bashretval = 'sqlplus-silent Scott/tiger <eofset pagesize 0 feedback off verify off heading off echo offselect * from EMP where ename = 'Scott '; exit; EOF 'If [-z "$ retval"]; then Echo "No rows returned from database" Exit 0 else echo $ retvalfioracle @ szdb: ~> Chmod U + x retval. shoracle @ szdb: ~> ./Retval. sh7788 Scott analyst 7566 19-apr-87 34171.88 202. method 2 for accepting SQL return values from shell variables Oracle @ szdb: ~> More./retval_2.sh #! /Bin/bash # Author: Robinson Cheng # blog: http://blog.csdn.net/robinson_0612sqlplus-s "Scott/tiger" <eofset pagesize 0 feedback off verify off heading off echo offcol CNT new_value v_cntselect count (*) CNT from EMP where deptno = 10; exit v_cnteofvalue = "$? "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/bashv_empno = "$1" sqlplus-s "Scott/tiger" <eofset pagesize 0 feedback off verify off heading off echo offselect ename from EMP where empno = $ v_empno; exiteofexitoracle @ szdb: ~> ./Retval_3.sh 7788 Scott

Ii. More references

For more information about user-managed backup and recovery, see

Oracle cold backup

Oracle Hot Backup

Concept of Oracle backup recovery

Oracle instance recovery

Oracle recovery based on user management (describes media recovery and processing in detail)

System tablespace management and Backup Recovery

Sysaux tablespace management and recovery

Oracle backup control file recovery (unsing backup controlfile)

 

For information on RMAN backup recovery and management, see

RMAN overview and architecture

RMAN configuration, Monitoring and Management

Detailed description of RMAN backup

RMAN restoration and recovery

Create and use RMAN catalog

Create RMAN storage script based on catalog

Catalog-based RMAN backup and recovery

RMAN backup path confusion (when using plus archivelog)

 

For the Oracle architecture, see

Oracle tablespace and data files

Oracle Password File

Oracle parameter file

Oracle online redo log file)

Oracle Control File)

Oracle archiving logs

Oracle rollback and undo)

Oracle database instance startup and Shutdown Process

Automated Management of Oracle 10g SGA

Oracle instances and Oracle databases (Oracle Architecture)