Question: What is an Oracle lexical parameter and how do I
pass a value to a PL/SQL stored procedure or function using lexical
parameters?
Answer: Lexical parameters are used with an ampersand (&) to
allow end-user input of variables, such as in SQL*Plus. In SQL*Plus,
you can accept a parameter and then reference it inside your query
by placing an ampersand in front of the variable name.
In the simple
example below, we declare a variable called myparm and direct
SQL*Plus to accept this value when the script is executed. For
more working examples of lexical parameters in the code download,
examples, see the book
Easy
Oracle SQL*Plus.
set heading off
set echo on
accept myparm
number prompt 'Choose a number between 1 and 10: '
select
'You chose the number '||&myparm from dual;
In PL/SQL lexical parameters are defined as variables and passed
with an ampersand:
variable mybindvar
number
begin
:mybindvar := &1;
myproc(:mybindvar);
end;
/
For more working examples of lexical parameters in PL/SQL
functions and stored procedures, see the book
Easy
Oracle PL/SQL. The code download
for the nook contains numerous working examples.
|
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|