Toad World® Forums

Pass subsitution parameter in Automation Designer

Message from: curtisbl_859

I have a need to pass a subsitution parameter during a script run from
automation designer. Is there any way to pass the &OWNER parameter to the script
without being prompted?

Thanks in advance.
curtisbl


Historical Messages

Author: curtisbl_859
Date: Tue May 17 07:44:05 PDT 2011
I have a need to pass a subsitution parameter during a script run from
automation designer. Is there any way to pass the &OWNER parameter to the script
without being prompted?

Thanks in advance.
curtisbl

__


I have a need to pass a subsitution parameter during a script run from
automation designer. Is there any way to pass the &OWNER parameter to
the script without being prompted?

You can:

define owner=value

Then, when &OWNER or &&OWNER is found it will be substituted.

Example:

define fred=barney

select ‘&fred’ from dual union all
select ‘&&fred’ from dual;


Cheers,
Norm. [TeamT]

[ Attachment(s) from curtisbl_859 included below]

Message from: curtisbl_859

Norm,
Thanks for the comment. I can use the define command within the scripts;
however, I’m looking to define or set the variable in automation designer
and have it picked up when the script hits the &OWNER or &&OWNER. Do
you know of a way to do that?


Historical Messages

Author: curtisbl_859
Date: Tue May 17 08:46:46 PDT 2011
Norm,
Thanks for the comment. I can use the define command within the scripts;
however, I’m looking to define or set the variable in automation designer
and have it picked up when the script hits the &OWNER or &&OWNER. Do
you know of a way to do that?

__

Author: Norman Dunbar
Date: Tue May 17 07:59:58 PDT 2011

I have a need to pass a subsitution parameter during a script run from >
automation designer. Is there any way to pass the &OWNER parameter to > the
script without being prompted? You can: define owner=value Then, when &OWNER or
&&OWNER is found it will be substituted. Example: define fred=barney select
‘&fred’ from dual union all select ‘&&fred’ from dual; – Cheers, Norm. [TeamT]
__

Author: curtisbl_859
Date: Tue May 17 07:44:05 PDT 2011
Message from: curtisbl_859 I have a need to pass a subsitution parameter during
a script run from automation designer. Is there any way to pass the &OWNER
parameter to the script without being prompted? Thanks in advance. curtisbl
_______________________________________ Historical Messages Author: curtisbl_859
Date: Tue May 17 07:44:05 PDT 2011 I have a need to pass a subsitution parameter
during a script run from automation designer. Is there any way to pass the
&OWNER parameter to the script without being prompted? Thanks in advance.
curtisbl __ _______________________________________
__


Attachment(s) from curtisbl_859

1 of 1 Photo(s)

SNAG-0009.jpg

Curtis (I think!)

Thanks for the comment. I can use the define command within
the scripts; however, I’m looking to define or set the
variable in automation designer and have it picked up when
the script hits the &OWNER or &&OWNER. Do you know of a way
to do that?

If you need the automation designer (which I don’t use often) to run a
script which has “&” or “&&” in it, then you need some way to supply
those values at run time. There are a couple of ways and if we assume
&OWNER is all we need then we have:

  1. Supply at run time on the command line:

    sqlplus user/password@database @script_name.sql MY_OWNER

Then inside script_name.sql we have this:

define owner=&1

Alternatively, you can replace all occurrences of &owner with &1.

  1. Supply parameter as above to a master script:

    sqlplus user/password@database @master_script.sql MY_OWNER

Then inside master_name.sql we have this:

define owner=&1
@@script_name.sql

And inside script_name.sql you use &owner or &&owner as normal.

Other than that, I can’t think of anything else that would allow you to
supply parameters at runtime - without user intervention.

HTH

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Norm,
Thanks for the comment. I can use the define command within the scripts; however, I'm looking to define or set the variable in automation designer and have it picked up when the script hits the &OWNER or &&OWNER. Do you know of a way to do that?

I know this is a very old post I’m replying to, but I was wondering if with newer versions of Toad, if there is a way within Automation Designer to pass a parameter to a script.