Q

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

PL/SQL expert expert Dan Clamage gives sample code for using EXECUTE IMMEDIATE with substitution variables in PL/SQL.

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

Example :

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.

This was first published in April 2009

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close