Re: Using Substitution Variables Dynamically in File Names

sfitzgerald02_at_gmail.com wrote:
> Sorry, I should have provided more info on the variables.
>
> /*
> ##############################################
> #
> # COMMON VARIABLES
> #
> ##############################################
> */
> var companyid varchar2(11);
>
> begin
> select &companyid into :companyid from dual;
> end;
> /
>
> set pages 9999
> set linesize 160
> set feedback off
>
> column tm new_value file_time noprint
> select to_char(sysdate,'YYYYMMDD') tm from dual;
>
> col sn noprint new_value company_name
> select scompanyname sn from tblcompany where ncompanyid= :companyid
> spool /home/utils/companyReports/&&company_name.&file_time..txt
>
> prompt -- &&company_name --;
>
> What results is as follows:
>
> -- -- <-- This should read something like '-- Acme --'.
>
> The file does not spool which I suspect is because the
> &&company_name.&file_time..txt does not result in a valid file name.
> The file_time variable does work though.
>

Hi Steve,

If you want to combine variables like you do in the file spec, you 'll need to use a concatenation character to delimit the variable names. You 'll have to set the character using the SET command in SQL*Plus (SET CONCAT <character>). I wouldn't know what this SPOOL specification does to your variables, but I suspect this is the source of the issue.

Regards,

Ruud de Koter. Received on Wed Oct 17 2007 - 09:11:38 CDT