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"