SQL & PL/SQL

Hi

Currently im using the attached procedure to genearte a CSV file,
wihout using utl_file package is there any way to write such file please assist.

create or replace
procedure sp_chkfunc_list_employee as

fp utl_file.file_type;
filename varchar2(100);
errmsg varchar2(2000);
err_lvl number :=0;
recnt number :=0;
--cursor to select different employee code

cursor c1 is select
pk_tb_chkemp_mst from tb_chkemp_mst
where avail_status ='a';
-- cursor to select atributes of the employee for respective employee code
cursor c2(emp_mst varchar) is
select fd.employee_name,fd.title,fd.job_name,fd.vacancy_to_retain,
pv1.its_description days,
pv2.its_description type,
pv3.its_description view_mode,
decode(result, 'p', 'pass','h', 'hold') result,
pv4.its_description mail_send,
pv5.its_description notice_board,
pv6.its_description privacy,
decode(func_users_only_flag, 'y','yes','n','no') func_users_only_flag,
decode(let_migrate_flag, 'y','yes','n','no') let_migrate_flag,
decode(let_immigrate_flag, 'y','yes','n','no') let_immigrate_flag,
decode(let_check_flag, 'y','yes','n','no') let_check_flag,
(select count(dd.pk_tb_chkjob_details) from tb_chkjob_details dd
where dd.fk_tb_chkemployee_details = fd.pk_tb_chkemployee_details
and dd.avail_status ='a'
and dd.job_deleted_flag='n')vacancy_available,
to_char(fd.changed_on, 'dd-mm-yy hh24:mi:ss') changed_on
from tb_chkemployee_details fd,tb_chkits_value pv1,tb_chkits_value pv2,
tb_chkits_value pv3,tb_chkits_value pv4,tb_chkits_value pv5,
tb_chkits_value pv6
where fd.avail_status = 'a'
and pv1.avail_status ='a'
and pv2.avail_status ='a'
and pv3.avail_status ='a'
and pv4.avail_status ='a'
and pv5.avail_status ='a'
and pv6.avail_status ='a'
and fd.days = pv1.pk_tb_chkits_value
and fd.type = pv2.pk_tb_chkits_value
and fd.view_mode = pv3.pk_tb_chkits_value
and fd.mail_send =pv4.pk_tb_chkits_value
and fd.notice_board =pv5.pk_tb_chkits_value
and fd.privacy = pv6.pk_tb_chkits_value
and fd.fk_tb_chkemp_mst = emp_mst;

file_path varchar2(100);

begin -- procedure begins


for c1_rec in c1
loop

select its_description into file_path
from tb_chkits_value pv,tb_chkfunctional_grp pg
where pv.fk_tb_chkfunctional_grp = pg.pk_tb_chkfunctional_grp
and pg.fk_tb_chkemp_mst = c1_rec.pk_tb_chkemp_mst
and pv.avail_status = 'a'
and pg.functional_grp = 'rpt';

filename := 'chkfunc_list_employee.'||to_char(sysdate, 'dd-mm-yy.hh24:mi:ss')||'.'||c1_rec.pk_tb_chkemp_mst||'.csv';
-- file is opened
fp := utl_file.fopen(file_path,filename,'w');
-- prints the file header
utl_file.putf(fp,'employee name,employee title,job name,count to retain,days to retain,type,view_mode type,result,email notification,message board notification,privacy classification,func only,let migrate,let immigrate,let check,count of vacancy available,last updated date/time,');
utl_file.new_line(fp);
-- for every employee code individual file is generated
for c2_rec in c2(c1_rec.pk_tb_chkemp_mst)
loop -- for every employee attributes are printed
utl_file.putf(fp,c2_rec.employee_name||','||c2_rec.title||','||c2_rec.job_name||','||c2_rec.vacancy_to_retain||','||
c2_rec.days||','||c2_rec.type||','||c2_rec.view_mode||','||c2_rec.result||','||
c2_rec.mail_send||','||c2_rec.notice_board||','||c2_rec.privacy||','||
c2_rec.func_users_only_flag||','||c2_rec.let_migrate_flag||','||c2_rec.let_immigrate_flag||','||
c2_rec.let_check_flag||','||c2_rec.vacancy_available||','||c2_rec.changed_on||',');

utl_file.new_line(fp);
end loop;
utl_file.new_line(fp);
utl_file.fclose(fp); --file is closed
end loop;
exception
when others then
fp := utl_file.fopen(file_path,'error_trace_emp_list_'||filename,'w');
utl_file.put_code(fp,'sp_chkfunc_list_employee failed due to :'||sqlcode||sqlerrm);
utl_file.put_code(fp,errmsg);
utl_file.fclose(fp);
utl_file.fclose(fp);

end;


Regards,
yazhini

Edited by: user8707806 on Sep 23, 2009 3:07 AM

Edited by: user8707806 on Sep 23, 2009 3:13 AM

Edited by: user8707806 on Sep 23, 2009 3:44 AM