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

PRO+

Content

Find more PRO+ content and other member only offers, here.

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