Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Dynamic spool file name

Re: Dynamic spool file name

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 13 Oct 2006 11:03:39 -0700
Message-ID: <1160762619.710216.110360@m7g2000cwm.googlegroups.com>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US