Turn: 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

Chapte 2:

Sometimes you have to write scripts that are mixed by the shell and Sqlplus for work.

In general, the shell variables into the SQL script, more convenient, but some of the results of SQL output to the shell, it is more troublesome. The method used to compare the soil, is in the sqlplus inside, spool to a temporary file, and then in the shell with Grep,awk class to analyze the output file. Later on the Internet to see an introduction, benefited AH. Thank you for that.

http://hi.baidu.com/edeed/blog/item/291698228a5694f4d7cae2c1.html/cmtid/e87926977f74636155fb968f

I tried three, one was to exit Sqlplus and return the value in passing. This method has a limit, only a number, not a string.

sql> col global_name new_value xxx
Sql> select Global_name from Global_name;

Global_name
--------------------------------------------------------------------------------
XXX.XXX.XXX

sql> Exit XXX
Sp2-0584:exit variable "XXX" was non-numeric

The second one is the output of the direct SELECT statement.

The script is as follows (test1.sh):

#!/bin/bash
Value= ' sqlplus-s user/[email protected] <<eof
Set heading off feedback off pagesize 0 Verify off echo off Numwidth 4
SELECT * from Global_name;
Exit
EOF '

Echo $VALUE

The test results are as follows:

[Email protected] tmp]# sh test1.sh
XXX.XXX.COM
[Email protected] tmp]#

The third one is to define a variable and print it in Sqlplus.

The script is as follows (test2.sh):

#!/bin/bash

Value= ' sqlplus-s user/[email protected] <<eof
Set heading off feedback off pagesize 0 Verify off echo off Numwidth 4
var username varchar2 (30)
Begin
Select User Into:username from dual;
: Username: = ' username ' | | : username;
End
/

Print username
Exit
EOF '

Echo $VALUE

The test results are as follows:

[Email protected]]# sh test2.sh
Username user_a
[[Email protected]]#

Sqlplus: Several cases of passing values to and from the shell