Working with substitution variables and using EXECUTE IMMEDIATE in PL/SQL

Can I use a variable in place of table name in a SELECT Statement?

Example :

define a = 'EMP';

SELECT  * FROM &A;

    Requires Free Membership to View

    Login

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.

This was first published in April 2009

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top