Substitution variables are a powerful feature in Oracle SQL*Plus that allow for dynamic query execution. They enable users to replace hard-coded values in SQL statements, allowing for more flexible and reusable scripts. In Oracle 19c, substitution variables or Dynamic Variables are particularly useful when dealing with dynamic SQL, where queries need to adapt based on user inputs or changing conditions.
What are Dynamic Variables?
Substitution variables, also referred to as SQL placeholders or parameter variables, are values that are dynamically inserted into SQL statements at runtime. Instead of hard-coding values into a query, you can use these variables to prompt the user for input or pull data from other queries. This flexibility is essential in creating scripts that can be reused across different datasets and environments.
For example, consider a query where you need to filter employee records based on their last name. Instead of manually entering the last name each time, you can use a Dynamic variable, prompting the user for input. This method not only saves time but also reduces the chances of errors in the query.
SQL> SELECT employee_id, last_name FROM employees WHERE last_name = '&lastname';
In this query, &lastname is a substitution variable that will prompt the user to enter a value at runtime. The value provided by the user is then substituted into the query, allowing for dynamic execution.
Creating Dynamic Variables
There are several ways to create substitution variables in Oracle SQL*Plus. One of the most common methods is by using the DEFINE command. The DEFINE command allows you to assign a value to a variable that can be referenced later in your script.
SQL> DEFINE dept = 'Sales';
SQL> SELECT employee_id, last_name FROM employees WHERE department_name = '&dept';
In this example, the dept variable is defined as ‘Sales’. When the query is executed, the &dept variable is replaced with the value ‘Sales’. This method is useful for setting default values that can be reused across multiple queries.
Another way to create substitution variables is by using the ACCEPT command. The ACCEPT command prompts the user to enter a value, which is then stored in the variable for later use.
SQL> ACCEPT dept PROMPT 'Enter department name: ';
SQL> SELECT employee_id, last_name FROM employees WHERE department_name = '&dept';
The ACCEPT command is particularly useful when you want to create interactive scripts that require user input.
Referencing Substitution Variables
Once a substitution variable is created, it can be referenced in any part of your SQL statement by prefixing the variable name with an ampersand (&). For example:
SQL> SELECT employee_id FROM employees WHERE last_name = '&lastname';
When this query is executed, Oracle SQL*Plus will prompt the user to enter a value for the lastname variable. The value provided by the user is then substituted into the query.
Using Substitution Variables in Dynamic SQL
Dynamic variables play a crucial role in dynamic SQL, where queries are constructed and executed at runtime. By using substitution variables, you can create SQL scripts that adapt to changing inputs, making them highly versatile and reusable.
For example, consider a scenario where you need to generate reports for different departments. Instead of writing a separate query for each department, you can use a substitution variable to dynamically generate the query based on the department name provided by the user.
SQL> ACCEPT dept PROMPT 'Enter department name: ';
SQL> SELECT employee_id, last_name, salary FROM employees WHERE department_name = '&dept';
In this example, the user is prompted to enter the department name, and the query is dynamically generated based on the input. This method is not only efficient but also reduces the chances of errors in the query.
Using SQL Placeholders and Parameter Variables
In addition to substitution variables, Oracle 19c supports the use of SQL placeholders and parameter variables. These variables allow for even greater flexibility in query execution, as they can hold values that are passed into the query at runtime.
SQL placeholders, also known as bind variables, are used to pass values into SQL statements. Unlike Dynamic variables, which are replaced before the query is executed, bind variables are passed into the query at runtime, making them more efficient for repeated executions.
SQL> VARIABLE dept_bind VARCHAR2(30);
SQL> BEGIN :dept_bind := 'Sales'; END;
SQL> SELECT employee_id, last_name FROM employees WHERE department_name = :dept_bind;
In this example, the dept_bind variable is a bind variable that holds the value ‘Sales’. The query references the bind variable using a colon (:), and the value is passed into the query at runtime.
Differences Between Substitution and Bind Variables
While both substitution variables and bind variables are used to pass values into SQL statements, there are some key differences between the two. Substitution variables are replaced before the query is executed, making them ideal for ad-hoc queries and scripts. On the other hand, bind variables are passed into the query at runtime, making them more efficient for repeated executions.
One of the main advantages of bind variables is that they help reduce parsing overhead in the Oracle database. When a query with a bind variable is executed multiple times, the database only needs to parse the query once, significantly improving performance.
Using Dynamic Variables in PL/SQL
Dynamic variables are not limited to SQL queries; they can also be used in PL/SQL blocks. By using substitution variables in PL/SQL, you can create dynamic scripts that adapt to user input or changing conditions.
For example, consider a PL/SQL block that inserts data into a table based on user input:
SQL> ACCEPT emp_id PROMPT 'Enter employee ID: ';
SQL> BEGIN
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (&emp_id, 'John', 'Doe');
END;
/
In this example, the emp_id substitution variable is used to insert a value into the employee_id column. The value is provided by the user at runtime, allowing for dynamic data insertion.
System Variables Influencing Substitution Variables
Oracle SQL*Plus includes several system variables that can influence how Dynamic variables are processed. These system variables allow you to control the behavior of substitution variables and customize your SQL scripts.
For example, the SET DEFINE command controls the character used to prefix substitution variables. By default, the ampersand (&) is used as the prefix, but you can change this character using the SET DEFINE command.
SQL> SET DEFINE %;
SQL> SELECT employee_id FROM employees WHERE last_name = '%lastname';
In this example, the SET DEFINE % command changes the prefix character to a percent sign (%). The query then references the lastname substitution variable using the new prefix.
Another useful system variable is SET VERIFY, which controls whether SQL*Plus displays the old and new versions of the query when a Dynamic variable is used. By default, SET VERIFY is enabled, but you can disable it using the following command:
SQL> SET VERIFY OFF;
Disabling SET VERIFY can be useful when you want to suppress the display of substitution variable values in your query output.
Best Practices for Using Substitution Variables
When using substitution variables in Oracle 19c, it’s important to follow best practices to ensure that your scripts are efficient and easy to maintain. Here are some key best practices to keep in mind:
- Use meaningful variable names: When creating substitution variables, choose names that are descriptive and easy to understand. This will make your scripts more readable and easier to maintain.
- Limit the use of hard-coded values: Substitution variables are designed to replace hard-coded values in your SQL statements. Whenever possible, use Dynamic variables instead of hard-coding values into your queries.
- Use
SET DEFINEandSET VERIFYto control variable behavior: System variables likeSET DEFINEandSET VERIFYallow you to control how substitution variables are processed. Use these commands to customize the behavior of your scripts. - Test your scripts with different inputs: Before deploying a script that uses substitution variables, test it with a variety of inputs to ensure that it works as expected.
Conclusion
Substitution variables are a powerful tool for creating dynamic SQL scripts in Oracle 19c. By allowing users to replace hard-coded values with variables, these variables make it easier to write flexible, reusable queries. Whether you’re using substitution variables in SQL or PL/SQL, following best practices and understanding how system variables influence their behavior will help you create more efficient and maintainable scripts.
See more on Oracle’s website!
Be Oracle Database Certified Professional, this world is full of opportunities for qualified DBAs!
