(To) shell circumstances call sqlplus example

Jul 27 posted in Oracle (RSS), 108 views.

First, in the simplest shell call sqlplus.

$ Vi test1.sh

#! / bin / bash
sqlplus-S / nolog> result.log <set heading off feedback off pagesize 0 verify off echo off
conn u_test / iamwangnc
select * from tab;
exit
EOF

$ Chmod + x test1.sh
$ ./test1.sh

Second, the results of the implementation of sqlplus method passed to a shell

Note the use of the boss key sqlplus `paragraph, and given the variable on both sides of the equal sign can not have spaces.

$ Vi test2.sh

#! / bin / bash
VALUE = `sqlplus-S / nolog <set heading off feedback off pagesize 0 verify off echo off numwidth 4
conn u_test / iamwangnc
select count (*) from tab;
exit
EOF `
if [ "$ VALUE"-gt 0]; then
echo "The number of rows is $ VALUE."
exit 0
else
echo "There is no row in the table."
fi

$ Chmod + x test2.sh
$ ./test2.sh

Third, the results of the implementation of sqlplus method passed to the shell 2

Note the use of sqlplus paragraph col .. new_value .. the definition of the variables and parameters with exit, and then automatically assigned to the shell's $?

$ Vi test3.sh

#! / bin / bash
sqlplus-S / nolog> result.log <set heading off feedback off pagesize 0 verify off echo off numwidth 4
conn u_test / iamwangnc
col coun new_value v_coun
select count (*) coun from tab;
exit v_coun
EOF
VALUE ="$?"
echo "The number of rows is $ VALUE."

$ Chmod + x test3.sh
$ ./test3.sh

Fourth, the parameters passed to the shell procedure sqlplus

$ 1, said first parameter, sqlplus can be used directly, given the variable on both sides of the equal sign must be no space there can be no space.

$ Vi test4.sh

#! / bin / bash
NAME = "$ 1"
sqlplus-S u_test / iamwangnc <select * from tab where tname = upper ( '$ NAME');
exit
EOF

$ Chmod + x test4.sh
$ ./test4.sh Ttt

Five, in order to implement the safety requirements of each shell are hand-enter the password

$ Vi test5.sh

#! / bin / bash
echo-n "Enter password for u_test:"
read PASSWD
sqlplus-S / nolog <conn u_test / $ PASSWD
select * from tab;
exit
EOF

$ Chmod + x test5.sh
$ ./test5.sh

Six, in order to read from the document password security

Set permissions on the password file, only the user can read and write their own.

$ Echo 'iamwangnc'> u_test.txt
$ Chmod g-rwx, o-rwx u_test.txt
$ Vi test6.sh

#! / bin / bash
PASSWD = `cat u_test.txt`
sqlplus-S / nolog <conn u_test / $ PASSWD
select * from tab;
exit
EOF

$ Chmod + x test6.sh
$ ./test6.sh

- End --

Original Address http://hi.baidu.com/edeed/blog/item/291698228a5694f4d7cae2c1.html
Article Source: DIY tribe (http://www.diybl.com/course/3_program/shell/shelljs/20090211/155236.html)

Test the contents of their own:

#! / bin / sh
PATH = $ PATH: $ HOME / bin
export PATH
export ORACLE_SID = oravm
export ORACLE_BASE = / opt / oracle
export ORACLE_HOME = / opt/oracle/9.2.0
export LD_LIBRARY_PATH = / opt/oracle/9.2.0/lib: / lib: / usr / lib: / usr / local / lib: / usr/X11R6/lib
export TNS_ADMIN = / opt/oracle/9.2.0/network/admin
export ORA_NLS33 = / opt/oracle/9.2.0/ocommon/nls/admin/data
export ORACLE_OWNER = oracle
export ORACLE_TERM = xterm
export PATH = / usr / kerberos / sbin: / usr / kerberos / bin: / usr / local / sbin: / usr / local / bin: / sbin: / bin: / usr / sbin: / usr / bin: / root / bin: / opt/oracle/9.2.0/bin
export LANG = zh_CN.gb2312
export NLS_LANG = "Simplified Chinese_china". ZHS16GBK
export NLS_DATE_FORMAT = 'YYYY-MM-DD HH24: MI: SS'

/ * To / home / oracle / archlog / of all the documents (actually, archive log files) is read into system, combined with the data dictionary, and then write system.logmnr table) * /
for fname in / home / oracle / archlog / *; do
echo "Processing $ fname!"
echo "connect / as sysdba"> / home / oracle / temp.sql
echo "EXECUTE dbms_logmnr.add_logfile ( '$ fname', dbms_logmnr.addfile);">> / home / oracle / temp.sql
echo "EXECUTE dbms_logmnr.start_logmnr (DictFileName => '/ opt/oracle/9.2.0/Oraclelogs/v816dict.ora')">> / home / oracle / temp.sql
echo "insert into system.logmnr (" SCN "," CSCN "," TIMESTAMP "," COMMIT_TIMESTAMP "," THREAD # "," LOG_ID "," XIDUSN "," XIDSLT "," XIDSQN "," PXIDUSN "," PXIDSLT "," PXIDSQN "," RBASQN "," RBABLK "," RBABYTE "," UBAFIL "," UBABLK "," UBAREC "," UBASQN "," ABS_FILE # "," REL_FILE # "," DATA_BLK # ", "DATA_OBJ #", "DATA_OBJD #", "SEG_OWNER", "SEG_NAME", "SEG_TYPE", "SEG_TYPE_NAME", "TABLE_SPACE", "ROW_ID", "SESSION #", "SERIAL #", "USERNAME", "SESSION_INFO "," TX_NAME "," ROLLBACK "," OPERATION "," OPERATION_CODE "," SQL_REDO "," SQL_UNDO "," RS_ID "," SEQUENCE # "," SSN "," CSF "," INFO "," STATUS " , "REDO_VALUE", "UNDO_VALUE", "SQL_COLUMN_TYPE", "SQL_COLUMN_NAME", "REDO_LENGTH", "REDO_OFFSET", "UNDO_LENGTH", "UNDO_OFFSET") (select * from v \ $ logmnr_contents); ">> / home / oracle / temp.sql
echo "exit;">> / home / oracle / temp.sql
/ opt/oracle/9.2.0/bin/sqlplus / nolog @ / home / oracle / temp.sql
echo The SQL file $ fname finished!
echo ------------------------------------------------- --------------
done