Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Spooling from PL/SQL Block

448335Aug 5 2005 — edited Aug 5 2005
Hi,

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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 2 2005
Added on Aug 5 2005
5 comments
37,498 views