Can I use a variable in place of table name in a SELECT Statement?
define a = 'EMP'; SELECT * FROM &A
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 &&anew 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.
Dig Deeper on Using Oracle PL-SQL
Related Q&A from Dan Clamage
A reader asks Oracle expert Dan Clamage about a single date condition mucking up a dynamic query. Continue Reading
One reader asks expert Daniel Clamage about the PL/SQL to_date and to_char functions and how to properly convert date and string values. Continue Reading
Oracle PL-SQL expert Daniel Clamage answers a question about a problem with doing an “insert” in PL-SQL to create a table dynamically. Continue Reading