RelationalDBDesignRelationalDBDesign





PL/SQL Parameters   «Prev  Next»
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:
  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.


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 
select
    tab_col_name 
from
     mytab; 

prompt "Variable value is: &my_var_name"

The next lesson shows you examples of parameters used with SQL*Plus.