Several cases of sqlplus and shell passing value to each other

Original works, allow reprint, please be sure to use hyperlinks in the form of the original source of the article, author information and this statement. Otherwise, the legal liability will be investigated. http://nbmonster.blog.51cto.com/1977736/665767

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

This article is from the "to_be_monster_of_it" blog, make sure to keep this source http://nbmonster.blog.51cto.com/1977736/665767

Several cases of sqlplus and shell passing value to each other