Suppose you have a Student table that holds information about all students in a school. You want to display all students that share a specific first name. You want the user to be able to enter this first name and the script should take care of the rest. You could use:
SELECT
std_id
std_fname,
std_lname
FROM
student
WHERE
std_fname = '&fname';
Oracle will automatically ask the user to enter the desired value to substitute &fname with.
In a script in which you only reference &fname once, this works perfectly. However, sometimes you want to refer to this value more than once in the script. Oracle would ask you for the value each time.
A solution to this is to use double ampersands &&. The script would then say
WHERE
std_fname = '&&fname';
What Oracle does in this case is store the &&fname value in memory for the duration of the current session. That way, if in subsequent commands you need to use the same user-provided value, it will be readily available and require no further user input. The problem with this is, sometimes you want to use this same value, but you may at other times in the same session require the user to provide another value for it. If you call this script twice within the same session, it will use the same value the second time, without requiring the user's input.
The way around this is to use the PROMPT...ACCEPT clause.
PROMPT
ACCEPT FNAME PROMPT "Please enter the student's first name: "
Then you'd reference fname in this way:
WHERE
std_fname = '&fname';
This method will allow for user input every time the script is run, and allow for different first names to be used for comparison within the same session.
