Parameters (@ script_name var1, var2) are passed when SQL * Plus executes the script)

When using sqlplus to execute an SQL script, parameters are often passed to the script. Similar to passing parameters in shell scripts, we can also pass parameters to SQL scripts by directly following multiple consecutive parameters and separating them with spaces. This document describes the content and provides an example.

1. sqlplus help information

The following help information describes sqlplus-H <Start> is: @ <URL >|< FILENAME> [. <ext>] [<parameter>...] runs the specified SQL * Plus script from a Web server (URL) or the local file system (filename. EXT) with specified parameters that will be assigned to substitution variables in the script.

2. sqlplus calls the script with parameters at the shell prompt

SQL> select * from V $ version where rownum <2; banner selects Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit productionsql> insert into EMP (empno, ename, job) Select 8888, 'Bob chengg', 'dba 'from dual; SQL> commit; [Oracle @ linux1 ~] $ More test. SQL set linesize 160 select empno, ename, job from & 1 where upper (ename) = upper ('& 2'); exit; [Oracle @ linux1 ~] $ Sqlplus Scott/tiger @ rac11g @ test. SQL EMP scottold 1: Select empno, ename, job from & 1 where upper (ename) = upper ('& 2') New 1: Select empno, ename, job from EMP where upper (ename) = upper ('Scott ') empno ename job -------------- --------- 7788 Scott analyst -- note that single or double quotation marks are required for calls that contain spaces, example [Oracle @ linux1 ~] $ Sqlplus Scott/tiger @ test. SQL EMP 'Bob cheng' old 1: Select empno, ename, job from & 1 where upper (ename) = upper ('& 2') New 1: Select empno, ename, job from EMP where upper (ename) = upper ('Bob cheng') empno ename job ---------- ----------- 8888 Bob Cheng DBA [Oracle @ linux1 ~] $ Sqlplus Scott/tiger @ test. SQL EMP "Bob Cheng"

3. Call the script with parameters at the SQL prompt

SQL> @ test. SQL EMP scottsql> set linesize 160sql> select empno, ename, job from & 1 where upper (ename) = upper ('& 2 '); empno ename job ---------- ----------- 7788 Scott analystsql> exit; --> because the script has exit, the script exits after execution. If it is at an SQL prompt and does not need to exit, comment out the row.

4. Parameter-included scripts for calling PLSQL

[oracle@linux1 ~]$ more test2.sqlset serveroutput on;declare    v_sal  number;    v_emp  number:=&1;begin    select sal into v_sal from emp where empno=&1;    dbms_output.put_line('The sal is :'||to_char(v_sal));end;/exit; [oracle@linux1 ~]$ sqlplus scott/tiger @test2.sql 7788old   3:     v_emp  number:=&1;new   3:     v_emp  number:=7788;old   5:     select sal into v_sal from emp where empno=&1;new   5:     select sal into v_sal from emp where empno=7788;The sal is :3000PL/SQL procedure successfully completed.

5. Use the new_value clause

The essence of SQL> Col ename new_value v_name --> new_value is to assign the obtained result to v_name for subsequent calls of SQL> select ename from EMP where empno = 7788; ENAME----------SCOTTSQL> select ename, job, sal from EMP where ename = '& v_name'; old 1: Select ename, job, Sal from EMP where ename = '& v_name' New 1: Select ename, job, sal from EMP where ename = 'Scott 'ename job Sal ------------- ---------- Scott analyst 3000 -- use new_value -- Author: Robin in interactive mode Son Cheng -- The following script extracts part of the clip from awrrpti. SQL. -- Blog: http://blog.csdn.net/?son_0612=oracle@linux1 ~] $ More temp. sqlpromptprompt specify the report typeprompt ~~~~~~~~~~~~~~~~~~~~~~~ Prompt wocould you like an HTML report, or a plain text report? Prompt enter 'html' for an HTML report, or 'text' for plain textprompt defaults to 'html' column report_type new_value report_type; --> the new_value defined in this field is the key set heading off; select 'Type specified: ', lower (nvl (' & report_type ', 'html') report_type from dual; Set heading on; prompt "variable report_type value is: & report_type "SQL> @ temp. the interactive prompt specify the report type ~~~~~~~~~~~~~~~~~~~~~~~ Wocould you like an HTML report, or a plain text report? Enter 'html' for an HTML report, or 'text' for plain textdefaults to 'html' enter value for report_type: htmlold 1: Select 'Type specified :', lower (nvl ('& report_type', 'html') report_type from dualnew 1: Select 'Type specified: ', lower (nvl ('html', 'html ')) report_type from dualtype specified: HTML "variable report_type value is: HTML" --> the variable value is passed to report_type.

6. comprehensive use of new_value and & Symbol

-- The following script comes from Master Tom, which includes new_value and the use of substitution variables. -- This example shows the two methods [Oracle @ linux1 ~] $ More mystat. sqlset echo offset verify offcolumn value new_val v --> here the new_value method is used to pass the variable define S = "& 1" --> here the substitution variable is defined to accept the parameter set autotrace offselect. name, B. valuefrom v $ statname A, V $ mystat bwhere. statistic # = B. statistic # and lower (. name) like '%' | Lower ('& s ') | '%'/set echo on -- below is the second script -- the variables defined in the previous script are referenced in the second script, such as & S, & v. In fact, & S is a global variable [Oracle @ linux1 ~] $ More mystat2.sqlset echo offset verify offselect. name, B. value V, to_char (B. value-& V, '000000') difffrom v $ statname A, V $ mystat bwhere. statistic # = B. statistic # and lower (. name) like '%' | Lower ('& s') |' % '/set echo onsql> @ mystatsql> set echo offenter value for 1: parse time elapsedname value ---------------------------------------------------- ---------- parse time elapsed 83sql> Update EMP set sal = Sal + 200; 15 rows updated. SQL> @ mystat2sql> set echo off linesize 160 name V diff ---------------------------------------------------------- -------------- parse time elapsed 117 34

7. Summary
A. When SQL plus executes the script, its parameter transmission uses an alternative variable. For more information, see replace variables and SQL * Plus environment settings.
B. The new_value clause can pass variables in the script. For details, refer to: SQL basics --> Use of new_value.
C. When multiple parameters are passed, use spaces to separate them.
D. Use quotation marks to enclose the parameter with spaces.

 

For more information, see:

For more information about Oracle RAC, see
Use crs_setperm to modify the resource owner and permissions of RAC.
Use crs_profile to manage RAC resource configuration files
RAC database startup and Shutdown
Oracle RAC services
Services in Oracle Database 10g
Migrate datbase from single instance to Oracle RAC
Connect Oracle RAC to a specified instance
Oracle RAC load balancing test (combined with server and client)
Oracle RAC server connection Load Balance)
Load Balance)
Non-Default port listening configuration in Oracle RAC (listener. ora tnsnames. ora)
Oracle RAC Listener Configuration (listener. ora tnsnames. ora)
Configure RAC load balancing and Failover
CRS-1006, CRS-0215 fault case
Installing Oracle 10g RAC Based on Linux (RHEL 5.5)
Use runcluvfy to verify the Oracle RAC installation environment

For more information about the basics and concepts of Oracle network configuration, see:
Configure dynamic service registration for non-default ports
Configure sqlnet. ora to restrict IP Access to Oracle
Configure and manage Oracle listener logs
Set the Oracle listener password (listener)
Configure the Oracle client to connect to the database

For more information about user-managed backup and recovery, see
Oracle cold backup
Oracle Hot Backup
Concept of Oracle backup recovery
Oracle instance recovery
Oracle recovery based on user management
System tablespace management and Backup Recovery
Sysaux tablespace management and recovery
Oracle backup control file recovery (unsing backup controlfile)

For information on RMAN backup recovery and management, see
RMAN overview and architecture
RMAN configuration, Monitoring and Management
Detailed description of RMAN backup
RMAN restoration and recovery
Create and use RMAN catalog
Create RMAN storage script based on catalog
Catalog-based RMAN backup and recovery
RMAN backup path confusion
Use RMAN for recovery from different machine backups (WIN platform)
Use RMAN to migrate a file system database to ASM
Linux RMAN backup shell script
Use RMAN to migrate the database to a different machine

For the Oracle architecture, see
Oracle tablespace and data files
Oracle Password File
Oracle parameter file
Oracle online redo log file)
Oracle Control File)
Oracle archiving logs
Oracle rollback and undo)
Oracle database instance startup and Shutdown Process
Automated Management of Oracle 10g SGA
Oracle instances and Oracle databases (Oracle Architecture)