Can I use a variable in place of table name in a SELECT Statement?
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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
Expert Dan Clamage explains how to use SQL SELECT and SQL UNION ALL statements to sort and visualize a set of sales figures.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
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.