More on variables with Oracle's SQL*Plus

Speaker Notes

Slide 1:

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.

Slide 2:

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.

Slide 3:

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

Slide 4:

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

Slide 5:

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

Note that the user input is in CHAR.

Slide 6:

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.

Slide 7:

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.

Slide 8:

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.

Slide 9:

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

Slide 10:

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.