Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Dynamic spool file name
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 -- Received on Fri Oct 13 2006 - 13:03:39 CDT