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

On 25 Aug 2006 04:47:57 -0700, "Andreww" <andrew.whittam@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
aram_1 := '&1';
aram_2 := '&2';
end;
/
so you are using proper bind variables.

Toad should support both methods.

--
Sybrand Bakker, Senior Oracle DBA

Hi Sybrand - I am a bit confused...

if I had the following code:

select count(*)
from tblCustomers
where store_code='X189';

How would I implement the solution?

Thanks for your help.

Andrew


Sybrand Bakker wrote:
> On 25 Aug 2006 04:47:57 -0700, "Andreww" <andrew.whittam@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
> aram_1 := '&1';
> aram_2 := '&2';
> end;
> /
> so you are using proper bind variables.
>
> Toad should support both methods.
>
> --
> Sybrand Bakker, Senior Oracle DBA

On 25 Aug 2006 08:03:14 -0700, "Andreww" <andrew.whittam@gmail.com>
wrote:

>Hi Sybrand - I am a bit confused...
>
>if I had the following code:
>
>select count(*)
>from tblCustomers
>where store_code='X189';
>
>How would I implement the solution?
>
>Thanks for your help.
>
>Andrew


define p_store_code = '&1'
select count(*)
from tblCustomers
where store_code = '&p_store_code'

or

variable p_store_code varchar2(4);
begin
_store_code := '&1';
end;
/
select count(*)
from tblcustomers
where store_code = _store_code

in both cases you would call the script with
<script name> <space> 'X189'

If you don't provide the parameter you will be prompted.

Could you please refrain from top posting?

--
Sybrand Bakker, Senior Oracle DBA