Using input variables:
Oracle allows you to accept user input to determine what records to access. This can be done
by putting an & in front of a variable data name. A variable data name that has not been
defined will cause a prompt to the user asking for the contents of the variable. The example
below is asking for a specific record. The second example asks for all records that meet a
particular criteria. The processing that happens is as soon as the select is keyed in and the
user presses enter, the Enter value for the variable name prompt appears. The user enters the
prompt and they see the old version of the select as it was written followed by the new version
of the select with the keyed in variable inserted in place of the prompt. Then the output is
shown. Notice that with the first two examples, I was entering numbers so they don't have to
be in quotes.
SQL CODE:
SQL> select idno, name from donor where idno = &donorid;
Enter value for donorid: 11111
old 1: select idno, name from donor where idno = &donorid
new 1: select idno, name from donor where idno = 11111
IDNO NAME
----- ---------------
11111 Stephen Daniels
SQL> select idno, name from donor where idno >= &donorid;
Enter value for donorid: 12121
old 1: select idno, name from donor where idno >= &donorid
new 1: select idno, name from donor where idno >= 12121
IDNO NAME
----- ---------------
12121 Jennifer Ames
22222 Carl Hersey
23456 Susan Ash
33333 Nancy Taylor
34567 Robert Brooks
In the third and fouth examples, I want character/string data and so I need to enter quotes
either around the &variable in the select or around the actual data.
SQL CODE:
SQL> SELECT idno, name, state
2 FROM donor
3 WHERE state ='&statein';
Enter value for statein: MA
old 3: WHERE state ='&statein'
new 3: WHERE state ='MA'
IDNO NAME ST
----- --------------- --
11111 Stephen Daniels MA
23456 Susan Ash MA
33333 Nancy Taylor MA
34567 Robert Brooks MA
SQL> SELECT idno, name, state
2 FROM donor
3 WHERE state = &statein;
Enter value for statein: 'MA'
old 3: WHERE state = &statein
new 3: WHERE state = 'MA'
IDNO NAME ST
----- --------------- --
11111 Stephen Daniels MA
23456 Susan Ash MA
33333 Nancy Taylor MA
34567 Robert Brooks MA
In the fifth example, I am showing you that functions can be used with this. In this case I am
going to look for the initcap version of the city.
SQL CODE:
SQL> SELECT idno, name, city, state
2 FROM donor
3 WHERE city = INITCAP('&cityin');
Enter value for cityin: seekonk
old 3: WHERE city = INITCAP('&cityin')
new 3: WHERE city = INITCAP('seekonk')
IDNO NAME CITY ST
----- --------------- ---------- --
11111 Stephen Daniels Seekonk MA
In the sixth example, I again asked for a specific idno. Since I know that idno was defined as
a varchar2, I decided to use the quotes to take in the variable idno. It worked this way as
well.
SQL CODE:
SQL> SELECT idno, name, state
2 FROM donor
3 WHERE idno = '&donorid';
Enter value for donorid: 22222
old 3: WHERE idno = '&donorid'
new 3: WHERE idno = '22222'
IDNO NAME ST
----- --------------- --
22222 Carl Hersey RI
If you don't want to see the old, new information, you can use the SET VERIFY OFF command and
if you want to see it again you can change with SET VERIFY ON.
SQL CODE:
SQL> SET VERIFY OFF
SQL> SELECT idno, name, city, state
2 FROM donor
3 WHERE city = INITCAP('&cityin');
Enter value for cityin: providence
IDNO NAME CITY ST
----- --------------- ---------- --
12121 Jennifer Ames Providence RI
22222 Carl Hersey Providence RI
This concept can be carried even further. For example I can substitute in column names, the
table name and the entire condition. In the second example, I even substituted in the field
to order by and in the third example I substituted in everything but the SELECT.
SQL CODE:
SQL> SELECT idno, name, &fstcol, &sndcol
2 FROM &tablename
3 WHERE &condname;
Enter value for fstcol: city
Enter value for sndcol: state
old 1: SELECT idno, name, &fstcol, &sndcol
new 1: SELECT idno, name, city, state
Enter value for tablename: donor
old 2: FROM &tablename
new 2: FROM donor
Enter value for condname: state = 'RI'
old 3: WHERE &condname
new 3: WHERE state = 'RI'
IDNO NAME CITY ST
----- --------------- ---------- --
12121 Jennifer Ames Providence RI
22222 Carl Hersey Providence RI
SQL> SELECT idno, name, &testcol
2 FROM &tablename
3 WHERE &condition
4 ORDER BY &ordname;
Enter value for testcol: yrgoal
old 1: SELECT idno, name, &testcol
new 1: SELECT idno, name, yrgoal
Enter value for tablename: donor
old 2: FROM &tablename
new 2: FROM donor
Enter value for condition: state = 'MA'
old 3: WHERE &condition
new 3: WHERE state = 'MA'
Enter value for ordname: yrgoal
old 4: ORDER BY &ordname
new 4: ORDER BY yrgoal
IDNO NAME YRGOAL
----- --------------- ---------
33333 Nancy Taylor 50
34567 Robert Brooks 50
23456 Susan Ash 100
11111 Stephen Daniels 500
SQL> SELECT &selname;
Enter value for selname: idno, name, yrgoal from donor where yrgoal > 50
old 1: SELECT &selname
new 1: SELECT idno, name, yrgoal from donor where yrgoal > 50
IDNO NAME YRGOAL
----- --------------- ---------
11111 Stephen Daniels 500
12121 Jennifer Ames 400
23456 Susan Ash 100
If you want to define a field so you can reuse the variable name without prompting, use the &&.
SQL CODE:
SQL> SELECT idno, name, &&col
2 FROM donor
3 WHERE &&col = 'MA';
Enter value for col: state
old 1: SELECT idno, name, &&col
new 1: SELECT idno, name, state
old 3: WHERE &&col = 'MA'
new 3: WHERE state = 'MA'
IDNO NAME ST
----- --------------- --
11111 Stephen Daniels MA
23456 Susan Ash MA
33333 Nancy Taylor MA
34567 Robert Brooks MA
SQL> SELECT idno, name, city, &&col
2 FROM donor;
old 1: SELECT idno, name, city, &&col
new 1: SELECT idno, name, city, state
IDNO NAME CITY ST
----- --------------- ---------- --
11111 Stephen Daniels Seekonk MA
12121 Jennifer Ames Providence RI
22222 Carl Hersey Providence RI
23456 Susan Ash Fall River MA
33333 Nancy Taylor Fall River MA
34567 Robert Brooks Fall River MA
6 rows selected.
To clear a reusable variable you can use the UNDEFINE statement.
SQL CODE:
SQL> undefine col;