Spool Command in Loop

[haseebkhatri]

How we use spool command with particular path in Cursor or Loop


Kindly reply ASAP

[haseebkhatri]

<b>Kindly make this spool command into LOOP</b>



set feed off markup html on spool on
spool excelfile.xls
select * from your_table;
spool off
set markup html off spool off


Kindly reply me it's help ful or not
Regards

[haseebkhatri]

CONNECT SCOTT/TIGER@ORCL


CREATE OR REPLACE DIRECTORY HASEEB AS '\\khisorasql10\ETMS1\SECURITY\HASEEB';




CREATE OR REPLACE PROCEDURE test_M
AS
v_output_file1 utl_file.file_type;
BEGIN
FOR FIRST_LOOP IN (SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP) LOOP
v_output_file1 := utl_file.fopen('HASEEB', FIRST_LOOP.deptno||'.csv', 'W');
FOR cursor_emp IN (SELECT empno,ename,deptno,sal FROM emp WHERE deptno=FIRST_LOOP.DEPTNO)
LOOP
utl_file.put_line(v_output_file1,
cursor_emp.empno || ',' || cursor_emp.ename||','||cursor_emp.sal||','||cursor_emp.deptno);
EXIT WHEN CURSOR_EMP.EMPNO IS NULL;
END LOOP;
utl_file.fclose_all;
END LOOP;
END;