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: <fitzjarrell_at_cox.net>
Date: 13 Oct 2006 14:10:47 -0700
Message-ID: <1160773847.249219.21560@k70g2000cwa.googlegroups.com>

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;

Then UTL_FILE may be your only option, as spool is not a SQL statement, it's a SQL*Plus command and is not available within a PL/SQL block:

DECLARE
   fileID UTL_FILE.FILE_TYPE;
   strbuffer VARCHAR2(100);
   cursor get_emp_stuff is
   select empno, ename, deptno, sal
   from emp;
BEGIN
   for e_rec in get_emp_stuff loop

        fileID := UTL_FILE.FOPEN ('d:\temp', e_rec.ename, 'W');
        strbuffer :=
e_rec.empno||','||e_rec.ename||','||e_rec.deptno||','||e_rec.sal;
        UTL_FILE.PUT_LINE (fileID, strbuffer);
        utl_file.fclose(fileID);

   end loop;
END;
/

This code created 14 different files in d:\temp (one for each ENAME), each containing details from the EMP table for the named employee.

David Fitzjarrell Received on Fri Oct 13 2006 - 16:10:47 CDT

Original text of this message

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