Question:
:
I have a set of SQL statement that are identical except for the
value of a variable.
I would like to make this SQL variable as a parameter and pass it to
the SQL.
What ways does Oracle have to allow passing a parameter to a SQL
query?
Answer:
If you want to learn to do this correctly, with
examples, get the
Easy Oracle Pack, with working code examples in the book
downloads.
Oracle is the most powerful of all databases, and it is easy to pass
a variable into a SQL statement using several methods, including
SQL*Plus and PL/SQL:
SQL*Plus script to pass a variable
You can pass variable as a parameter in SQL*Plus with the PROMPT and
ACCEPT parameters something like this script that accepts
emp_name as a parameter to pass to the SQL:
ACCEPT empame
PROMPT 'Enter Employee Name: '
SELECT * FROM emp WHERE
ename = ?&empame?;
PL/SQL script to pass a variable
In PL/SQL you can
pass a parameter to a PL/SQL stored procedure or function:
set feedb off
create
or replace directory tmp as '/tmp';
declare
inFile utl_file.file_type;
outFile
utl_file.file_type;
x varchar2(40);
begin
inFile := utl_file.fopen('TMP','in','R');
outFile :=
utl_file.fopen('TMP','out','W');
utl_file.put_line(outFile,'Enter a value for x
: ');
utl_file.fflush(outFile);
utl_file.get_line(inFile,x);
utl_file.put_line(outFile,'you entered '||x);
utl_file.fclose(inFile);
utl_file.fclose(outFile);
end;
/
quit
$
mknod /tmp/out p; mknod /tmp/in p
$ (cat /tmp/out &);(sqlplus -s scott/tiger
@interactiveplsql &
);
cat>/tmp/in
Enter
a value for x :
ABC123
|
|
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.
|