Unix Shell_shell Call Sqlplus Introduction (case)

2014-06-20 Baoxinjian

I. Summary

If you develop a UNIX shell in Oracle EBS, it is bound to involve calling Plsql in the shell and calling Plsql in the shell typically by sqlplus this tool

About Sqlplus need to understand Sqlplus's login and common commands, specific in the introduction of Sqlplus usage

1. How to login Sqlplus

Sqlplus [[<option>] [<logon>] [<start>]
<option>: [-C <version>] [-l] [-m] <options> "] [-R <level>] [-S]

• -C <version> Sets the compatibility of the affected commands to the <version> specified version. This version has the format "X.y[.z". For example,-C 10.2.0
• -L attempts to log on only once, rather than prompting again when an error occurs.
• -M "<options>" sets the automatic HTML markup for the output. The format of the option is: HTML [on| OFF] [HEAD text] [BODY text] [TABLE text] [Entmap {on| OFF}] [SPOOL {on| OFF}] [Pre[format] {on| OFF}]
• -R <level> Set restricted mode to disable the Sql*plus command that interacts with the file system. The level can be 1, 2, or 3. The maximum limit level is-R 3, which disables all user commands that interact with the file system.
• -S set the silent mode, the mode is hidden, the command's Sql*plus flag, Prompt and echo display.

2. Basic commands for Login Sqlplus

• Sql> Show All-View all 68 system variable values
• Sql> Show user--show current connected users
• Sql> Show Error--Show errors
• Sql> set heading off-suppresses output column headers, default value on
• Sql> set feedback off-suppresses the last-line count feedback, with the default value "6 or more records, loopback on"
• Sql> set timing on-default is off, set query time-consuming, can be used to estimate the execution times of SQL statements, test performance
• Sql> set Sqlprompt "sql>"--Set default prompt, default value is "Sql>"
• Sql> set Linesize 1000--Set screen display line width, default 100
• Sql> set autocommit on--Set whether to submit automatically, default to OFF
• Sql> set pause on--default is off, set pause, will stop the screen display, wait for the ENTER key, and then display the next page
• Sql> set arraysize 1--Defaults to 15
• Sql> set Long 1000--default is 80

3. Sqlplus syntax

The grammatical structure is basically consistent with the plsql, skipping

Second, the case

1. The simplest way for the shell to invoke Sqlplus

Step1. Code

1 #!/bin/bash2 sqlplus-s/nolog > Result.log <<EOF30 Echo off 4 Conn apps/apps5'bxjsqlplus1.sh'null ); 6 Exit 7 EOF

Step2. Perform

. ./bxjshellsql1.sh

Step3. Results

2.1 Pass the value in Sqlplus to the Shell method one

Step1. Code

1#!/bin/Bash2Value= ' Sqlplus-s/nolog <<EOF3Set heading off feedback off pagesize0Verify offEchoOff Numwidth44Conn apps/Apps5 SelectCOUNT (*) from Bxj_sqlplus_test;6 Exit7 EOF '8 if["$VALUE"-gt0]; Then9   Echo "The number of rows is $VALUE."TenExit0 One Else A   Echo "There is no row in the table." - fi

Step2. Perform

. ./bxjshellsql2.sh

Step3. Results

2.2 Pass the value in SQL Plus to the Shell method two

Step1. Code

1#!/bin/Bash2Sqlplus-s/nolog > Result.log <<EOF3Set heading off feedback off pagesize0Verify offEchoOff Numwidth44Conn apps/Apps5 Col coun New_value V_coun6 SelectCOUNT (*) Coun from Bxj_sqlplus_test;7 Exit V_coun8 EOF9Value="$?"Ten Echo "The number of rows is $VALUE."

Step2. Perform

. ./bxjshellsql3.sh

Step3. Results

3. Pass the values in the shell to the Sqlplus

Step1. Code

1 #!/bin/bash2 comments="$"3 sqlplus-s Apps/apps <<EOF4'bxjsqlplus4.sh'$COMMENTS ' ); 5 Exit 6 EOF

Step2. Perform

. ./bxjshellsql4.sh

Step3. Results

、

Abalone New ********************

Reference: Network data http://www.blogjava.net/xzclog/archive/2010/04/01/317151.html