This continues the information on variables with Oracle SQL*Plus. NOTE: SQL*Plus goes far beyond the common specifications for SQL.

Remember from the previous presentation that you can request user input by putting an & or && in front of the variable name. The single & is for the current select while the && allows you to reuse the variable that has been saved without prompting the user each time.

Remember when using order by, the default is ascending.

In this example, the user is not entering actual data. The user is entering the names of columns/fields that will be used when the SELECT is executed.

Remember to clear the stored variable you use UNDEFINE col since col is the name I used with the && --- &&col.

You can use the DEFINE and ACCEPT commans in SQL*Plus to predefine variables.

DEFINE followed by a variable name can be used to check the definition of a variable.

Note that the user input is in CHAR.

Note that this time the defined variable is being used for data.

The defined variable colin refers to a column/field.

The defined variable jobci refers to the data literal CI.

The ACCEPT takes user input and stores it in the variable. Datatype can be specified along with format and prompt.

See notes for more information.

Variables can be set up to take user input as the SELECT is being run or they can be predefined using the DEFINE or the ACCEPT.

This uses the datatype, format and prompt features of ACCEPT to take in data as a variable.

To suppress the display of the old and new, you can set verify off. When you want to see them again you can set verify on.

Set verify is essentially a toggle switch.