Using the VERIFY Command in Oracle SQL

Topic Introduction: In Oracle SQLPlus, the VERIFY command is used to control whether or not SQLPlus should display the values of substitution variables on the screen as you enter them. It can be used to enable or disable the echoing of substitution variables during script execution or interactive sessions. This command is particularly useful when you want to hide sensitive data, such as passwords, from being displayed on the screen.

Here's how to use the VERIFY command in Oracle SQL*Plus:

Enable Substitution Variable Echo (Default Behavior):

By default, SQL*Plus displays the values of substitution variables as you enter them. If you want to ensure that substitution variable values are displayed, you can use the VERIFY ON command:

VERIFY ON

After executing this command, SQL*Plus will display substitution variable values on the screen.

Disable Substitution Variable Echo:

If you want to hide the values of substitution variables when you enter them, you can use the VERIFY OFF command:

VERIFY OFF

After executing this command, SQL*Plus will not display substitution variable values on the screen as you enter them. This can be useful when you're entering sensitive information like passwords.

Toggle Substitution Variable Echo:

You can toggle the echoing of substitution variable values using VERIFY without any arguments. For example:

VERIFY

This will switch between enabling and disabling substitution variable echo depending on its current state.

View Current VERIFY Setting:

To check the current status of the substitution variable echoing, you can use the SHOW VERIFY command:

SHOW VERIFY

It will display either "ON" or "OFF" to indicate whether substitution variable echoing is currently enabled or disabled.

Here's an example of how the VERIFY command can be used in practice:

DEFINE username = my_username

DEFINE password = my_password

-- Disable substitution variable echoing to hide the password

VERIFY OFF

-- Prompt for the username and password without displaying them

ACCEPT user_prompt PROMPT 'Enter username: '

ACCEPT pass_prompt PROMPT 'Enter password: ' HIDE

-- Enable substitution variable echoing again

VERIFY ON

-- Display the values of the substitution variables

PRINT username

PRINT password

In this example, VERIFY OFF is used to hide the password while prompting the user for input. Afterward, VERIFY ON is used to re-enable echoing so that the values of the substitution variables can be displayed with PRINT.