Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Dynamic spool file name
fitzjarrell_at_cox.net wrote:
> Mike C wrote:
> > 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');
> > end;
>
>
>
> >
I was going to do that, but I am at a client and don't have access to the UNIX box to get the resulting file.
I wound up writing a script that generates a script with the necessary Spool and SQL. Received on Fri Oct 13 2006 - 16:28:51 CDT