Re: Parameter substitution question

From: John P. Higgins <jh33378_at_deere.com>
Date: Wed, 12 Aug 1998 20:41:57 -0500
Message-ID: <35D24464.BDDAEC4F@deere.com>

Here is the solution I sent directly to Ariel, who found that it worked and wanted me to post it for the group:

VARIABLE this_month char(3)
VARIABLE last_month char(3)

BEGIN
 :this_month:= to_char(SYSDATE,'Mon');
 :last_month:= to_char(add_months(SYSDATE,-1),'Mon'); END;
/
column v1 new_value current_month
column v2 new_value prev_month

select :this_month v1, :last_month v2 from dual;

@mytab &current_month &prev_month

Here are the results:

SQL> @mstr

PL/SQL procedure successfully completed.

V1                               V2
-------------------------------- --------------------------------
Aug                              Jul

old   3:   &1     number default 0,
new   3:   Aug     number default 0,
old   4:   &2     number default 0,
new   4:   Jul     number default 0,

old 5: last&1 number default 0)
new 5: lastAug number default 0)

Table created.

SQL> The key is to turn the (:) host_variables into (&) substitution_variables.

Ariel Kirson wrote:

> Hi all,
>
> I recently posetd a similar question, without
> any sample code. I hope this time it will be more
> clear. I have written the following script (monrep1.sql) :
>
> declare
> this_month char(3) := to_char(SYSDATE,'Mon');
> last_month char(3) := to_char(add_months(SYSDATE,-1),'Mon');
> this_year char(4) := to_char(SYSDATE,'YYYY');
> begin
> @monrep &this_month &last_month &this_year;
> end;
>
> When I run this script (@monrep1) I am prompted for the values
> of this_month, last_month and this_year. How can I send the
> values of the variables to "monrep.sql" ?
>
> Thanks in advance,
>
> Ariel Kirson
> Ex Libris.
Received on Wed Aug 12 1998 - 20:41:57 CDT