Using Input Variables - Speaker Notes
This presentation deals with user input and variables.
This slide shows user input to determine what records to retrieve.
As soon as the select is keyed in and the user presses Enter, the Enter value of the variable name prompt appears. The user enters the content of the variable at the prompt.
In this case note that it the user enclosed the entry in single quotes.
The user will then see the old version of the line containing the prompt followed by the new version of the line containing the prompt with the entry inserted in place of the prompt.
If the select encloses the prompt for the string variable in single quotes then the user does not have to use the single quotes when entering the string variable.
CHAR and VARCHAR2 columns/fields need single quotes as do date fields. Numeric fields do not.
Note that functions can be combined to retrieve the needed data.
Note that when you do not know the case used on the table/file or if there is a mixture of cases (some stored upper, some stored with initial caps etc.), then converting both sides of the condition to the same type maximizes your chance for success.
SELECT name, salary, jobcode
WHERE jobcode = 'CI';
This allows the user to specify all elements of the SELECT. This select could be saved and rerun with user entry.
Taking this to the extreme you can key in all information including FROM and WHERE.
In this example, the value of jobentry must be keyed in by the user. Since the user wants to have jobcode as the third field and then wants to use jobcode to test against CI, the user entes jobcode when prompted to:
Enter value for jobentry: