Re: Dynamic spool file name

Mark D Powell wrote:
> On Oct 13, 12:53 pm, "Mike C" <michaelj..._at_hotmail.com> wrote:
> > I need to create files based on data returned from a cursor. I know I
> > can use a variable and the column statement to dynamically set the
> > spool filename in a script. However, I am using cursors in a block and
> > that doesn't seem to work inside the block (I can't do a select in a
> > block).
>
> How about doing somtehing like this:
>
> set echo off
>
> column my_variable new_value myfilename
> variable v_charfld varchar2(20)
>
> begin
> select to_char(sysdate,'YYYYMMDD') into :v_charfld from dual;
> end;
> /
> print :v_charfld
>
> select :v_charfld as my_variable from dual;
> print v_charfld
>
> set pagesize 0
> set linesize 80
> set trimspool on
>
> spool constant_&&myfilename..txt
>
> select ename, empno, deptno from emp;
> spool off
> undefine &&myfilename
>
> In your case I think you might also want to use a reference cursor to
> hold the results of the query and you can use the print statement to
> write the reference cursor result set to the spool file.
>
> HTH -- Mark D Powell --

That doesn't work for me. I need to open the spool file within the block as the spool file name will change based on the result of the cursor recordset. I ran what is below and it prompts me for the variable and then errors out.

set echo off

column my_variable new_value myfilename
variable v_charfld varchar2(20)

begin

	  select to_char(sysdate,'YYYYMMDD') into :v_charfld from dual;
	  spool  constant_&&myfilename..txt
	  dbms_output.put_line('test');