how to use SPOOL in a stored Procedure?

How can I use SPOOL Command in a stored procedure to divert the output of a select statement to a file?You can't. You could use UTL_FILE to write to a file on the server. Or, if there is not too much output, you could use DBMS_OUTPUT in the stored procedure and SET SERVEROUT ON in SQL Plus before running it.|||Originally posted by andrewst
You can't. You could use UTL_FILE to write to a file on the server. Or, if there is not too much output, you could use DBMS_OUTPUT in the stored procedure and SET SERVEROUT ON in SQL Plus before running it.
Sorry dear friend by using ref cursor it's possible.|||Originally posted by amit_krai
Sorry dear friend by using ref cursor it's possible.
You think so? OK, if you can make the SQL Plus "SPOOL" command work from a stored procedure using a REF CURSOR, please share your code!|||Well if you mean something like this...
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> CREATE OR REPLACE PROCEDURE procedure_name (
2 column_name IN VARCHAR2,
3 ref_cursor OUT SYS_REFCURSOR)
4 IS
5 BEGIN
6 OPEN ref_cursor FOR
7 ' SELECT SUM (sal), ' || column_name ||
8 ' FROM emp GROUP BY ' || column_name;
9 END;
10 /

Procedure created.

SQL> SET AUTOPRINT ON;
SQL> VARIABLE ref_cursor REFCURSOR;
SQL> SPOOL emp_groups.lst
SQL> EXEC procedure_name ('DEPTNO', :ref_cursor);

PL/SQL procedure successfully completed.

SUM(SAL) DEPTNO
---- ----
8750 10
10875 20
9400 30

SQL>
...then that is not calling SPOOL from a PL/SQL procedure - the stored procedure has finished executing, the output is being SPOOLed by SQL*Plus, not by PL/SQL. Perhaps we are splitting hairs - can the original poster confirm whether this is what they meant?

Padders|||For Spooling, create a batch file and call it into your procedure.