The SET and SHOW Commands with SQL

Using Ampersand Substitution to Restrict and Sort Output at Run Time: The SET and SHOW Commands

Introduction to SET and SHOW Commands

In Oracle SQL*Plus:

• SET is used to configure SQL*Plus settings and behaviors, including those related to substitution variables.
• SHOW is used to display the current settings of SQL*Plus parameters, including those related to substitution variables.

Using the SET Command for Substitution Variables

The SET command can configure various aspects of SQL*Plus, including how substitution variables are handled. Key settings include DEFINE, VERIFY, and TERMOUT.

Syntax: 

SET parameter value;

Key Parameters for Substitution Variables

SET DEFINE

The SET DEFINE command specifies the character that SQL*Plus uses to recognize substitution variables. The default character is the ampersand (&), but you can change this if needed.

Syntax: 

SET DEFINE character;

 Example: Change the Substitution Character 

SET DEFINE $;
SELECT name, salary
FROM employees
WHERE department = '$dept_name'
ORDER BY salary $sort_order;

In this example, $dept_name and $sort_order are used instead of &dept_name and &sort_order for substitution.

SET VERIFY

The SET VERIFY command controls whether SQLPlus displays the new value of a substitution variable after it has been replaced. By default, VERIFY is ON, which means SQLPlus shows the replaced values. Setting it to OFF hides this information.

Syntax: 

SET VERIFY {ON | OFF};

Example: Turn Off Variable Verification 

SET VERIFY OFF;
SELECT name, salary
FROM employees
WHERE department = '&dept_name'
ORDER BY salary &sort_order;

In this example, the SET VERIFY OFF command prevents SQL*Plus from displaying the resolved values of &dept_name and &sort_order.

SET TERMOUT

The SET TERMOUT command controls whether SQL*Plus outputs to the terminal or not. This can be useful for scripts that generate reports or run batch jobs.

Syntax: 

SET TERMOUT {ON | OFF};

Example: Turn Off Terminal Output 

SET TERMOUT OFF;
SELECT name, salary
FROM employees
WHERE department = '&dept_name'
ORDER BY salary &sort_order;

In this example, SET TERMOUT OFF prevents SQL*Plus from displaying the output to the terminal, which is useful when running scripts where output is redirected to a file or handled differently.

Using the SHOW Command

The SHOW command displays the current setting of various SQL*Plus parameters. This is useful for verifying the current configuration related to substitution variables.

Syntax: 

SHOW parameter;

Key Parameters to Show

SHOW DEFINE

Displays the current character used for substitution variables.

Example: 

SHOW DEFINE;

 This command shows the character currently set for substitution variables (e.g., & or $).

SHOW VERIFY

Displays whether the VERIFY setting is ON or OFF.

Example: 

SHOW VERIFY;

This command shows if SQL*Plus is currently set to display the values of substitution variables after they are replaced.

SHOW TERMOUT

Displays whether terminal output is ON or OFF.

Example: 

SHOW TERMOUT;

This command shows if SQL*Plus is currently set to output results to the terminal.

Combining SET and SHOW for Interactive Queries

You can use SET to configure your SQL*Plus environment and SHOW to verify settings. This is particularly useful when preparing scripts or running interactive queries.

Example: Interactive Query Script 

-- Set the substitution character to $
SET DEFINE $;
-- Turn off variable verification
SET VERIFY OFF;
-- Disable terminal output (if running in batch mode)
SET TERMOUT OFF;
-- Display current settings (for verification)
SHOW DEFINE;
SHOW VERIFY;
SHOW TERMOUT;
-- Interactive query
ACCEPT dept_name PROMPT 'Enter Department Name: ';
ACCEPT sort_order PROMPT 'Enter Sort Order (ASC/DESC): ';
-- Execute the query using user-provided values
SELECT name, salary
FROM employees
WHERE department = '$dept_name'
ORDER BY salary $sort_order;

In this example:

• SET DEFINE $; changes the substitution character.
• SET VERIFY OFF; suppresses the display of variable replacements.
• SET TERMOUT OFF; prevents terminal output (optional, based on context).
• SHOW commands verify current settings.
• ACCEPT commands prompt for user input.
• The query uses the provided values to filter and sort data.

Best Practices

• Use DEFINE for Default Values: Define default values for variables to streamline query execution.
• Manage Output Visibility: Use SET VERIFY and SET TERMOUT to control what information is displayed and where.
• Verify Settings with SHOW: Use SHOW commands to confirm current settings and ensure they align with your expectations.

Conclusion

The SET and SHOW commands in Oracle SQL*Plus provide powerful tools for managing substitution variables and controlling query behavior. By configuring these settings, you can create more flexible and interactive SQL queries that adapt to different user inputs and requirements.