Several cases of sqlplus and shell passing value to each other

Several cases of sqlplus and shell passing value to each other

Scenario One: The simplest invocation of sqlplus in the shell

$cat test.sh

#!/bin/sh

Sqlplus Oracle/[email Protected]>file.log <<eof

SELECT * from Test;

Exit

EOF #注意EOF要顶格写

$sh test.sh

$cat File.log

--Omit a number of system prompt information-------

Sql>

EMPNO EmpName SAL DEPTNO

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

10002 Frank Naude 500 20

10001 Scott Tiger 1000 40

--Omit a number of system prompt information-------

Redirects the execution to file File.log, which can be viewed through cat file.log

Case two: Assign the value of Sqlplus directly to the shell variable

$cat test.sh

#!/bin/sh

# output The sqlplus result to the variable value

# Use of SET command can be queried manual

#注意shell中等号两边不能有空格

Value= ' Sqlplus-s/nolog <<eof

Set heading off feedback off pagesize 0 verify off Echo off

Conn Oracle/[email protected]

Select COUNT (*) from test;

Exit

EOF '

#输出记录数

echo "The number of rows is $VALUE."

$sh test.sh

The number of rows is 2.

Display results are correct, table test total 2 Records

Scenario Three: Indirectly assigns the value of the sqlplus to the shell variable

$cat test.sh

#!/bin/sh

#利用COL column New_value variable defining variables

#sqlplus执行完后最后返回值为v_coun

#利用 $? Assigns the last return value to value, which is the number of records for test

Sqlplus-s/nolog <<eof

Set heading off feedback off pagesize 0 verify off Echo off

Conn Oracle/[email protected]

Col Coun New_value V_coun

Select COUNT (*) Coun from test;

Exit V_coun

Eof

Value= "$?"

echo "The number of rows is $VALUE."

$sh test.sh

2

The number of rows is 2.

The first 2 in the script execution result is a sqlplus return value, and the second 2 is the value

Scenario Four: Pass the value of the shell variable to sqlplus use

$cat test.sh

#!/bin/sh

#sqlplus引用shell变量TABLENAME的值

#注意赋值时, no spaces on either side of the equals sign

Tablename= "Test"

Sqlplus-s Oracle/[email protected] <<eof

SELECT * FROM ${tablename};

Exit

$sh test.sh

EMPNO EmpName SAL DEPTNO

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

10002 Frank Naude 500 20

10001 Scott Tiger 1000 40

The result of the script execution is: SELECT * from Test;

Scenario Five: Manually entering shell variable values interactively

$cat test.sh

#!/bin/sh

#将手工输入变量值读入变量TABLENAME

echo "Enter the TableName want to select:"

Read TABLENAME

Sqlplus-s Oracle/[email protected] <<eof

SELECT * FROM ${tablename};

Exit

$sh test.sh

#按提示输入表名test

Enter the TableName want to select:

Test

EMPNO EmpName SAL DEPTNO

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

10002 Frank Naude 500 20

10001 Scott Tiger 1000 40

Execution result of the script execution as select * from Test

Several cases of sqlplus and shell passing value to each other