Re: Spooling from pl/sql procedure

From: Joel R. Kallman <jkallman_at_us.oracle.com>
Date: Thu, 07 Jan 1999 19:21:26 GMT
Message-ID: <369608b9.18528132_at_newshost.us.oracle.com>


You could use the UTL_FILE functions and procedures to write this to a file, as in:

declare
cursor c1 is select ename, job from emp; l_file UTL_FILE.FILE_TYPE;
begin

    l_file := UTL_FILE.FOPEN( '/tmp','file1.lis','w');     for x in c1 loop

        UTL_FILE.PUT_LINE( l_file, 'EName: ' || x.ename || ' Job: ' || x.job );

    end loop;
    UTL_FILE.FCLOSE( l_file );
end;
/

Don't forget that you will need to update the UTL_FILE_DIR parameter in your initialization file.

This is well-documented in Chapter 12 of the Oracle8 Server Application Developer's Guide.

On Thu, 7 Jan 1999 13:42:31 -0500, "Sunder" <Sunder.nochilur_at_pcm.bosch.com> wrote:

>I am trying to spool the output of a select statement from inside a pl/sql
>procedure to a file. See below for the code. SPOOL does not work form within
>a procedure.Can anybody tell me how I can accomplish this.
>
>
>declare
>mailid varchar2(100);
>cursor c1 is select name from table1;
>begin
> for orec in c1 loop
> spool file1.lis
> select * from table2 where name = orec.name;
> end loop;
>end;
>
>
>

Thanks!

Joel

Joel R. Kallman Oracle Service Industries

Columbus, OH            
jkallman_at_us.oracle.com                   http://www.oracle.com



The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Received on Thu Jan 07 1999 - 20:21:26 CET

Original text of this message