Use of Parameters in SQL*Plus and PL/SQL

Parameters play a crucial role in Oracle SQL*Plus and PL/SQL by facilitating dynamic interaction, enhancing modularity, improving reusability, and bolstering performance.

In SQL*Plus, parameters can be used in several ways to facilitate interactive and dynamic SQL execution:

1. Bind Variables: These are variables you can create in SQLPlus and then use in PL/SQL blocks. You can also use bind variables in SQL queries. Bind variables hold specific values and can be referenced in multiple places within a SQLPlus session.
2. Substitution Variables: Substitution variables can replace SQL*Plus command options and can also be used to store temporary values to modify the behavior of scripts and commands. When a substitution variable is encountered, its value is substituted in place before the command is executed.
3. System Variables: System variables control the environment of your SQLPlus session. You can change their values to alter the behavior of SQLPlus, such as changing the display format for date or time or controlling the number of lines on a page.

1. Procedure and Function Parameters: PL/SQL procedures and functions use parameters to pass values between calling and called subprograms. These parameters can be classified as IN, OUT, and IN OUT parameters. IN parameters pass values to the subprogram, OUT parameters return values to the caller, and IN OUT parameters do both.
2. Default Parameters: These parameters in procedures or functions have a default value. If no argument is passed for such a parameter when calling the procedure or function, the default value is used.
3. Parameter Aliasing: In PL/SQL, the ALIAS directive allows the caller to specify the parameter's value by the parameter name, making the code more understandable and reducing the chance of passing arguments in the wrong order.
4. VARIADIC Parameters: Introduced in Oracle 18c, VARIADIC parameters allow a procedure or function to accept a variable number of parameters, thereby increasing the flexibility of subprograms.

Overall, parameters in Oracle SQL*Plus and PL/SQL play a vital role in enhancing code usability, readability, flexibility, and performance. Their effective use enables developers to create more robust, efficient, and maintainable code.

A parameter is a value passed to a PL/SQL stored object or to a SQL*Plus script when it is executed. All types of PL/SQL stored objects except the triggers can use parameters. Any SQL*Plus script can contain parameters. You can use parameters in SQL*Plus and PL/SQL to:

1. Provide the start and end dates when creating a SQL*Plus report
2. Send a username or password to SQL*Plus to make a connection within a script
3. Provide a primary key value to a procedure so that the proper data is found
4. Return a calculated value from a procedure.

Parameters can be of nearly any datatype that you can define within PL/SQL and SQL.
Parameters are nearly always used when you create a function, procedure, or package as a way to pass information between the calling application and the called sub-program. Parameters are used less often in SQL*Plus, but can greatly increase the flexibility of SQL*Plus scripts you create.

While Oracle provides some powerful ways to get data into a database sometimes we will want to pass things directly from our shell scripts. There are a couple methods we can use for this.

SQL*Plus has a new_value argument to the column clause, used to capture SQL results and pass them back as variable in SQL*Plus. In this example we query for tab_col_name and place it into the my_var_name variable:

column tab_col_name new_value my_var_name 
select
  tab_col_name 
from
  mytab; 

prompt "Variable value is: &my_var_name"