Re: SQL*PLUS Variable assignment
From: Scott Mattes <smattes_at_InterserF.net>
Date: 1995/09/08
Message-ID: <42pc42$ki5_at_ns.interserf.net>#1/1
> variable en char(12)
> DECLARE
> v_en char(4);
> BEGIN
> select to_char(sysdate,'MMDD')into v_en from dual;
> :en := 'cmms' || v_en || '.lst';
> END;
> /
Variables that are created with the VARIABLE command cannot be used as SQL*Plus substitution variables (stupid of Oracle, I know, but that's the way it is.) Basically, all you can do with them outside of PL/SQL is PRINT them. But there is another way to do the same thing:
Date: 1995/09/08
Message-ID: <42pc42$ki5_at_ns.interserf.net>#1/1
snowden_at_haven.ios.com (Roger Snowden) wrote:
>Is it even possible to dynamically assign variables in sql*plus? We have
>a developer that wants to create a spool file name dynamically,
>constructed from SYSDATE, like: 0905something.rpt.
>Is this doable?
Roger,
Yes, it is and it was just discussed on Oracle-l. Here is the
example I choose to use for a diff application (setting sqlprompt to
current instance/user).
> variable en char(12)
> DECLARE
> v_en char(4);
> BEGIN
> select to_char(sysdate,'MMDD')into v_en from dual;
> :en := 'cmms' || v_en || '.lst';
> END;
> /
Variables that are created with the VARIABLE command cannot be used as SQL*Plus substitution variables (stupid of Oracle, I know, but that's the way it is.) Basically, all you can do with them outside of PL/SQL is PRINT them. But there is another way to do the same thing:
column v_en noprint new_value en
set head off feed off term off
select to_char(sysdate,'MMDD') v_en from dual;
set head on feed on term on
spool cmms&en..lst
/* The rest of your routine goes here. */
Received on Fri Sep 08 1995 - 00:00:00 CEST