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: