Spooling from PL/SQL Block
448335Aug 5 2005 — edited Aug 5 2005Hi,
Is it possible to do spooling from a pl/sql block.The spool cannot be the first command because I want to give the spool file name in a loop and each time the spool name should change based on the value.
I tried all possiblities,but I couldn't can anyone give me some idea on how to do this.
I cannot use UTL_FILE option ,because the client agreed only for spooling.
Is it possible to write the spool data is excel format..
Please find below an example of my requirement..
DECLARE
CurSor Cur_Designation IS
Select Designation from tbl_Designation;
N_Desig Cur_Designation%RowType;
Cursor Cur_Emp(Desig Number) IS
Select Emp_no,Emp_name
From Employee Where Emp_Designation = Desig;
N_Emp Cur_Emp%RowType;
BEGIN
Open Cur_Designation ;
Loop
Fetch Cur_Designation INTO N_Desig;
EXIT WHEN Cur_Designation %NOTFOUND;
--Here I want to give the spool file name
Spool --Should be designation name
Open Cur_Emp(N_Desig.Designation);
Loop
Fetch Cur_Emp INTO N_Emp ;
EXIT WHEN Cur_Emp %NOTFOUND;
-- All employee details I need to come to log file
End Loop;
Close Cur_Emp;
End Loop;
Close Cur_Desingnation;
End;
Please advice me how to do this..
Thanks,
Bindu