Parameters (@ script_namevar1, 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.

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.

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 how to use sqlplus to call a script with parameters.
Sqlplus-H

Is :@ | [. ] [ ...]

Runs the specified SQL * Plus script from a web server (URL) or
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
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

SQL> insert into emp (empno, ename, job) select 8888, 'Bob cheng', '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 scott

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 ('Scott ')

EMPNO ENAME JOB
-----------------------------
7788 SCOTT ANALYST

-- Note that single or double quotation marks are required for calls with spaces. The following 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"