Search code examples
sqloracle-databasetoad

TOAD for Oracle, variables for schema/ table names

I have a list of SQL queries that compares several tables between two schemas.

Is there a way to define variables with schema names? So every month one can update the variables on the top of the script? I'm looking for something like that:

define schema_name1="CALC1111";
define schema_name1="CALC2222";
select a.*, b.* 
from &schema_name1.TABLE_1 a left join
&schema_name2.TABLE_1 
on <-- some ON statements -->
Solution

  • Well, if TOAD uses SQL*PLUS syntax for substitution variables, then you should be able to do this already. For example, in SQLDeveloper, I run the following script frequently:

    set define on;
define t_table=ENTER_TABLE_NAME_HERE;
define t_schema=MYSCHEMA;
create public synonym &t_table for &t_schema..&t_table;

    Note two things:

    • You can use no quotes when defining, the variable will be substituted before the text is parsed, as-is. Double quotes will work fine too, but you'll be actually executing something like select a.*, b.* from "CALC1111".TABLE_1 a left join ...
    • You'll have to use double period (..), because a single period is used to separate a substitution variable from the following text if no whitespace is used, so your current text is resolved to select a.*, b.* from "CALC1111"TABLE_1 a left join ... which obviously doesn't work.