Introduction to Unix Shell_Shell calling SQLPlus (case study), shell_shellsqlplus

Introduction to Unix Shell_Shell calling SQLPlus (case study), shell_shellsqlplus

2014-06-20 BaoXinjian

I. Summary

If you develop a Unix Shell in Oracle EBS, it will certainly involve calling PLSQL in the Shell. In Shell, calling PLSQL usually uses the SQLPlus tool.

For more information about SQLPlus, see SQLPlus logon methods and Common commands.

1. SQLPlus logon Method

Sqlplus [[<option>] [<logon>] [<start>]
<Option>: [-C <version>] [-L] [-M "<options>"] [-R <level>] [-S]

• -C <version> sets the compatibility of affected commands to the specified version. This version is in the "x. y [. z]" format. For example,-C 10.2.0
• -L only attempts to log on once, instead of prompting again when an error occurs.
• -M "<options>" sets the output automatic HTML Tag. The format of the options is: HTML [ON | OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON | OFF}] [SPOOL {ON | OFF}] [PRE [FORMAT] {ON | OFF}]
• -R <level> sets the 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 is-R 3, which disables all user commands that interact with the file system.
• -S sets the no-Prompt mode. This mode is hidden. The SQL * Plus mark of the command is displayed, and the prompt and ECHO are displayed.

2. Basic commands for logging on to SQLPlus

• SQL> show all -- View all 68 system variable values
• SQL> show user -- display the current connected user
• SQL> show error -- Display error
• SQL> set heading off -- disable the output column title. The default value is ON.
• SQL> set feedback off -- disable counting feedback of the last row. The default value is "send back ON" for 6 or more records"
• SQL> set timing on -- the default value is OFF. It sets the query time, which can be used to estimate the SQL statement execution time and test the performance.
• SQL> set sqlprompt "SQL>" -- sets the default prompt. The default value is "SQL>"
• SQL> set linesize 1000 -- set the row width on the screen. The default value is 100.
• SQL> set autocommit ON -- sets whether to submit automatically. The default value is OFF.
• SQL> set pause on -- the default value is OFF. When this parameter is set to suspend, the screen will be stopped. Wait for the ENTER key to display the next page.
• SQL> set arraysize 1 -- default value: 15
• SQL> set long 1000 -- the default value is 80.

3. SQLPlus syntax

The syntax structure is basically the same as that of PLSQL, skipped

 

Ii. Case studies

1. The simplest Shell method to call SQLPlus

Step1. code

1 #!/bin/bash2 sqlplus -S /nolog > result.log <<EOF3 set heading off feedback off pagesize 0 verify off echo off4 conn apps/apps5 insert into bxj_sqlplus_test values (sysdate, 'bxjsqlplus1.sh', null);6 exit7 EOF

Step 2: Execute

> ../Bxjshellsql1.sh

Step3. result

 

2.1 pass the value in SQLPlus to Shell method 1

Step1. code

 1 #!/bin/bash 2 VALUE=`sqlplus -S /nolog <<EOF 3 set heading off feedback off pagesize 0 verify off echo off numwidth 4 4 conn apps/apps 5 select count(*) from bxj_sqlplus_test; 6 exit 7 EOF` 8 if [ "$VALUE" -gt 0 ]; then 9   echo "The number of rows is $VALUE."10   exit 011 else12   echo "There is no row in the table."13 fi

Step 2: Execute

> ../Bxjshellsql2.sh

Step3. result

2.2 pass the value in SQL Plus to Shell method 2

Step1. code

 1 #!/bin/bash 2 sqlplus -S /nolog > result.log <<EOF 3 set heading off feedback off pagesize 0 verify off echo off numwidth 4 4 conn apps/apps 5 col coun new_value v_coun 6 select count(*) coun from bxj_sqlplus_test; 7 exit v_coun 8 EOF 9 VALUE="$?"10 echo "The number of rows is $VALUE."

Step 2: Execute

> ../Bxjshellsql3.sh

Step3. result

 

3. Pass the value in Shell to SQLPlus

Step1. code

1 #!/bin/bash2 COMMENTS="$1"3 sqlplus -S apps/apps <<EOF4 insert into bxj_sqlplus_test values (sysdate, 'bxjsqlplus4.sh', '$COMMENTS');5 exit6 EOF

Step 2: Execute

> ../Bxjshellsql4.sh

Step3. result

,

 

********************Author: Bao Xin********************

 

 

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