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 -->
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:
select a.*, b.* from "CALC1111".TABLE_1 a left join ...
..
), 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.