To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
Substitution variables only work in SQL*Plus or SQL*Worksheet, because those tools know about substitution variables. This is OK for a one-off script you're running manually. For automation, it's not so good because it'll sit there at a prompt waiting for input. Since you define the substitution variable first, simply "double-up" your ampersands:
SQL> define a= 'DUAL';
SQL> select sysdate from &&a;
old 1: select sysdate from &&a
new 1: select sysdate from DUAL
SYSDATE
---------
30-MAR-09
You can use command-line substitution variables &1, &2, &3 etc. in lieu of defining them inline in the script, if you're calling your script from some other context, such as a Korn Shell script.
In PL/SQL, you can do this dynamically with Execute Immediate. This is a trivial example, but it illustrates the general programming style I use with dynamic SQL.
declare
c_stmt constant varchar2(100) := 'select sysdate from ~t';
v_stmt varchar2(200);
v_date date;
begin
v_stmt := replace(c_stmt, '~t', 'dual');
dbms_output.put_line(v_stmt);
execute immediate v_stmt into v_date;
dbms_output.put_line(v_date);
end;
/
select sysdate from dual
30-MAR-09
I like to build a template of the SQL I'm trying to generate dynamically. I'll code a concrete sample query and test it to make sure it works. Then I use my own "substitution placeholders" (a single character prefixed with tilde) to indicate what I'm replacing. The tilde-character doesn't mean anything; it's just a sequence that's unlikely to appear in an SQL statement.
By using a template, reusing it for different dynamic queries is much easier.
If your dynamic SQL has a syntax error, printing it out before executing it is the most "immediate" way to see the mistake quickly.
|
