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: Mike C <michaeljc70_at_hotmail.com>
Date: 13 Oct 2006 14:28:51 -0700
Message-ID: <1160774931.401796.153100@e3g2000cwe.googlegroups.com>

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;

>

> 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

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

Original text of this message

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