Re: Using Substitution Variables Dynamically in File Names

From: <fitzjarrell_at_cox.net>
Date: Tue, 16 Oct 2007 11:09:56 -0700
Message-ID: <1192558196.432584.15100@e34g2000pro.googlegroups.com>

On Oct 16, 11:56 am, sfitzgeral..._at_gmail.com wrote:
> I'm looking to use a substitution variable in a file name. Here is
> what I have so far.
>
> column sn new_value company_name noprint
> select scompanyname into :company_name from tblcompany where
> ncompanyid = :companyid
> spool /home/utils/companyReport/&company_name.&file_time..txt
>
> prompt -- &company_name --;
>
> The variable &company_name doesn't appear to be working. Any help
> would be appreciated.
>
> Thanks.
>
> Steve

Certainly it works:

SQL> create table tblcompany(
2 sn varchar2(40),
3 ncompanyid number);

Table created.

SQL>
SQL> insert all
  2 into tblcompany
  3 values ('ACME', 1)
  4 into tblcompany
  5 values ('Merkle Snertz', 17)
  6 into tblcompany
  7 values
  8 ('Quart Low',44)
  9 into tblcompany
10 values ('Big Dipper Zippers', 9)
11 select * from dual;

4 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> column sn new_value company_name
SQL> variable companyid number
SQL> exec :companyid := 17

PL/SQL procedure successfully completed.

SQL>
SQL> select sn from tblcompany where
2 ncompanyid = :companyid
3 /

Merkle Snertz

SQL>
SQL> prompt -- &&company_name --;
-- Merkle Snertz --
SQL> Why you're using SELECT ... INTO ... in SQL*Plus is a mystery, as it's silently ignored outside of PL/SQL.

David Fitzjarrell Received on Tue Oct 16 2007 - 13:09:56 CDT