Interaction between Shell and sqlplus

Interaction between Shell and sqlplus 1. Simple shell calls sqlplus

Write scripts

[Oracle @ localhost oracle_script] $ vimtest1.sh

#! /Bin/bash

# User env
Source ~ /. Bash_profile

Sqlplus-S/nolog> result. Log <EOF

-- Set heading off feedback off pagesize 0 verify off echo off

Conn Scott/Tiger

Select * from EMP where empno = 7369;

Exit

EOF

Note: add source ~ here ~ /. Bash_profile environment variable; otherwise, the sqlplus command cannot be found,

You can also directly use the export... Oracle path.

Modify script Permissions

[Oracle @ localhost oracle_script] $ chmod + x test1.sh

[Oracle @ localhost oracle_script] $ LL

Total 4

-Rwxr-XR-x 1 Oracle oinstall 154 01-1308: 59 test1.sh

 

Execute scripts

[Oracle @ localhost oracle_script] $./test1.sh

 

View results

[Oracle @ localhost oracle_script] $ catresult. Log

Empno ename job Mgr hiredate Sal comm deptno

------------------------------------------------------------------------------

7369 Smith clerk 7902 17-dec-80 800 20

 

2. Return the sqlplus value to shell (1)

Method 1: Execute sqlplus with the shell panel key:

[Oracle @ localhost oracle_script] $ VI test2.sh

#! /Bin/bash

# User env
Source ~ /. Bash_profile

Value='Sqlplus-S/nolog <EOF

Set heading off feedback off pagesize 0 verify off echo off numwidth 4

Conn Scott/Tiger

Select count (*) from EMP;

Exit

EOF'

If ["$ value"-GT 0]; then

Echo "the number of rows is $ value ."

Exit 0

Else

Echo "there is no row in thetable ."

Fi

 

Note: there must be no spaces on both sides of the equal sign. If there is a space, the following error will be reported:

[Oracle @ localhost oracle_script] $./test2.sh

./Test2.sh: Line 2: Value: Command notfound

./Test2.sh: Line 8: [: integer expressionexpected

There is no row in the table.

 

Modify script Permissions

[Oracle @ localhost oracle_script] $ chmod + x test2.sh

[Oracle @ localhost oracle_script] $ LL

Total 4

-Rwxr-XR-x 1 Oracle oinstall 154 01-1308: 59 test2.sh

 

View results

[Oracle @ localhost oracle_script] $./test2.sh

The number of rows is 14.

3. Return the sqlplus value to shell (2)

This sqlplus uses the col column name new_value variable name to define the variable with the parameter exit.V_counReturns $?

[Oracle @ localhost oracle_script] $ cattest3.sh

#! /Bin/bash

# User env
Source ~ /. Bash_profile

Sqlplus-S/nolog> result. Log <EOF

-- Set heading off feedback off pagesize 0 verify off echo off numwidth 4

Conn Scott/Tiger

ColCounNew_valueV_coun

Select count (*)CounFrom EMP;

Select * from EMP where empno = 7369;

ExitV_coun

EOF

Value = "$? "

Echo "the number of rows is $ value ."

 

Note: new_value is generally used as follows:

Column column_name new_value var_name

New_value is to assign the obtained column value to the variable name, and then the variable name can be used for subsequent processing.

 

Modify script Permissions

[Oracle @ localhost oracle_script] $ chmod + x test3.sh

[Oracle @ localhost oracle_script] $ LL

Total 16

-RW-r -- 1 Oracle oinstall 5 01-16 15:10 result. Log

-Rwxr-XR-x 1 Oracle oinstall 213 01-1310: 00 test1.sh

-Rwxr-XR-x 1 Oracle oinstall 285 01-1615: 08 test2.sh

-Rwxr-XR-x 1 Oracle oinstall 259 01-1615: 10 test3.sh

 

View results

[Oracle @ localhost oracle_script] $./test3.sh

The number of rows is 14.

4. PASS Parameters to sqlplus by Shell

$1 is the first parameter passed to shell. It is passed to sqlplus. The equal sign 2 of the variable value expression cannot have spaces.

[Oracle @ localhost oracle_script] $ VI test4.sh

#! /Bin/bash

# User env
Source ~ /. Bash_profile

Name = "$1"

Sqlplus-s Scott/tiger <EOF

Select * from EMP where ename = upper ('$ name ');

Exit

EOF

 

 

Modify script Permissions

[Oracle @ localhost oracle_script] $ chmod + x test4.sh

[Oracle @ localhost oracle_script] $ LL

Total 4

-Rwxr-XR-x 1 Oracle oinstall 154 01-1308: 59 test4.sh

 

View results

[Oracle @ localhostoracle_script] $./test4.sh

No rows selected

[Oracle @ localhost oracle_script] $./test4.sh Smith

Empno ename job Mgr hiredate Sal comm deptno ----------------------------------------------------------------------------

7369 Smith clerk 7902 17-dec-80 800 20

5. Secure Shell calls sqlplus

To ensure database connection security, you must enter the user name and password each time you call the script.

[Oracle @ localhost oracle_script] $ VI test5.sh

#! /Bin/bash

# User env
Source ~ /. Bash_profile

Echo-n "Enter User name :"

Read uname

Echo-n "Enter Password foruser :"

Read passwd

Sqlplus-S/nolog <EOF

Conn $ uname/$ passwd

Select * from EMP;

Exit

EOF

 

[Oracle @ localhost oracle_script] $./test5.sh

Enter User name: Scott

Enter password for user: Tiger

Empno ename job Mgr hiredate Sal

--------------------------------------------------------------------

7369 Smith clerk 7902 17-dec-80 800

Remarks: organize data from the Internet