| Lesson 2 || What is a parameter? |
| Objective || Describe the uses of parameters in SQL*Plus and PL/SQL. |
Use of Parameters in SQL*Plus and PL/SQL
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:
- Provide the start and end dates when creating a SQL*Plus report
- Send a username or password to SQL*Plus to make a connection within a script
- Provide a primary key value to a procedure so that the proper data is found
- 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.
Passing variables to SQL*Plus
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.
The SQL*Plus new_value clause
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
prompt "Variable value is: &my_var_name"
The next lesson shows you examples of parameters used with SQL*Plus.