Spooling from pl/sql procedure

[Sunder]

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;

[Dennis Havermans]

Maybe take a look at your oracle manual (just look for the utl_file package)

Sunder wrote in message <772v4l$6qg$1...@news.fr.internet.bosch.de>...

[Van Nieuwenhuyse]

You can not spool from within a procedure since ... the procedure is
executed on
the server.

Instead you can use de dbms_output package (eg: dbms_output.put_line) to
buffer output into memory, ounce the procedure finished the output will be
printed
on your spool. Do not forget to execute the SET SERVEROUTPUT command first.
Also you may need to enlarge the dbms_output buffer (see doc).

Other way is of course to use UTL_FILE_IO (but this only writes on files
upon the
server).

Sunder heeft geschreven in bericht

[Joel R. Kallman]

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....@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
jkal...@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.