Re: Parameter substitution question

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: Mon, 10 Aug 1998 17:23:39 +0200
Message-ID: <6qn392$761$1@pascal.a2000.nl>

First of all, you can't use the @ sign like you tried. When you have a declare .. begin .. end, you are using PL/SQL. However, the @ or START command is not at all SQL or PL/SQL, but just a command that SQL*Plus interprets itself, without using Oracle at all. So, SQL*Plus can even execute your @ or START without being connected to Oracle.

However, even if you would have been able to do so, then you should not have used the & (ampersand) character. That's another thing which is not SQL or PL/SQL. Prior to actually sending the code to Oracle, SQL*Plus will replace all &xx values. In a PL/SQL block, you would simply have given the variable names, you declared in the lines above, without the &-sign.

As you want to invoke your monrep.sql by starting monrep1.sql, you need the START command. So, you can not use PL/SQL -- drop the declare .. begin .. end. Just to show some options:

1 - using a select which gives hidden output:

    column this_month new_value p_this_month noprint
    column last_month new_value p_last_month noprint
    column next_month new_value p_this_year noprint

    set verify off feedback off heading off

    select to_char(SYSDATE,'Mon') this_month,

        to_char(add_months(SYSDATE,-1),'Mon') last_month,
        to_char(SYSDATE,'YYYY') this_year

    from dual;

    set verify on feedback on heading on     @monrep &p_this_month &p_last_month &p_this_year

2 - using bind variables:

    variable this_month varchar2(3)
    variable last_month varchar2(3)
    variable this_year varchar2(4)
    begin
        :this_month := to_char(SYSDATE,'Mon');
        :last_month := to_char(add_months(SYSDATE,-1),'Mon');
        :this_year := to_char(SYSDATE,'YYYY');

    end;
    /
    @monrep :this_month :last_month :this_year

By the way: consider using varchar2 instead of char. It will keep you out of trouble when using LIKE etc.

Arjan.

>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;
Received on Mon Aug 10 1998 - 10:23:39 CDT