Download SQL*Plus User's Guide and Reference

Passing Parameters through the START Command
Example 5–11
Passing Parameters through START
To create a new script based on SALES that takes a parameter specifying the job to be
displayed, enter
GET SALES
1
2
3
4
5
6*
COLUMN LAST_NAME HEADING 'LAST NAME'
COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999
COLUMN COMMISSION_PCT HEADING 'COMMISSION %' FORMAT 90.90
SELECT LAST_NAME, SALARY, COMMISSION_PCT
FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN'
6
6* WHERE JOB_ID='SA_MAN'
CHANGE /SA_MAN/&1
6* WHERE JOB_ID='&1'
SAVE ONEJOB
Created file ONEJOB
Now run the command with the parameter SA_MAN:
START ONEJOB SA_MAN
SQL*Plus lists the line of the SQL command that contains the parameter, before and
after replacing the parameter with its value, and then displays the output:
old
new
3: WHERE JOB_ID='&1'
3: WHERE JOB_ID='SA_MAN'
LAST NAME
MONTHLY SALARY COMMISSION %
------------------------- -------------- -----------Russell
$14,000
0.40
Partners
$13,500
0.30
Errazuriz
$12,000
0.30
Cambrault
$11,000
0.30
Zlotkey
$10,500
0.20
You can use many parameters in a script. Within a script, you can refer to each
parameter many times, and you can include the parameters in any order.
While you cannot use parameters when you run a command with RUN or slash (/),
you could use substitution variables instead.
Before continuing, return the columns to their original heading by entering the
following command:
CLEAR COLUMN
5-18 SQL*Plus User's Guide and Reference