Re: Macro Substitution Variables in Oracle SQL

On 25 Aug 2006 04:47:57 -0700, "Andreww" <andrew.whittam_at_gmail.com> wrote:

>Hi - I use Toad as my SQL interface.
>
>I have loads of reports which run each month or where paramaters
>(campaign codes, store names etc) change with each report.
>
>As the SQL might be long and complex and the parameters to change could
>appear all over the SQL, I was hoping there was some way I could
>specify all the variable at the top of the script for the SQL to "pick
>up". Thence I don't have to work through the code and chenge the
>params.
>
>Anyone got any ideas?
>
>Thanks
>
>Andrew

In sql*plus you can
define param_1 = '&1'
define param_2 = '&2'
where &1 &2 etc (to a max of 9) are commandline parameters.

Better you would
variable param_1 <datatype>
variable param_2 <datatype>
begin
:param_1 := '&1';
:param_2 := '&2';
end;
/
so you are using proper bind variables.

Toad should support both methods.

--
Sybrand Bakker, Senior Oracle DBA