CREATE OR REPLACE PACKAGE batch_kkk11 AS
PROCEDURE batch_extract_kkk11(p_directory IN VARCHAR2 , p_filename IN VARCHAR2) ;
END batch_kkk11;
/
CREATE OR REPLACE PACKAGE BODY batch_kkk11 AS
PROCEDURE batch_extract_kkk11 (p_directory IN VARCHAR2 , p_filename IN VARCHAR2) AS
v_out_file utl_file.file_type;
v_record_count NUMBER:=2;
v_cnt NUMBER:=1;
v_rec varchar2(5000);
v_rec_type varchar2(10);
v_sep VARCHAR2(2):=CHR(199);
final_t Uii_File_Pack_kkk1.tbl_body;
ename1 kemp.ename%type;
last_update1 kemp.last_update%type;
dname1 kdept.dname%type;
last_update2 kdept.last_update%type;
city1 kcity.city%type;
last_update3 kcity.last_update%type;

CURSOR C_MAIN_CURSOR IS
select ename,kemp.last_update,dname,kdept.last_update,city,kcity.last_update from kcity,
kdept,kemp where kemp.empno=kdept.empno and kdept.deptno=kcity.deptno
and to_char(sysdate,'dd')-to_char(kemp.last_update,'dd')=1
and to_char(sysdate,'dd')-to_char(kdept.last_update,'dd')=1
and to_char(sysdate,'dd')-to_char(kcity.last_update,'dd')=1;
begin
--v_out_file:= utl_file.fopen('c:\temp','zafar'||'.dat','w');
Uii_File_Pack_kkk1.p_write_header_kkk1(p_directory,p_filename,v_out_file);
OPEN C_MAIN_CURSOR ;
LOOP
FETCH C_MAIN_CURSOR INTO
ename1,
last_update1,
dname1, 
last_update2,
city1, 
last_update3
EXIT WHEN C_MAIN_CURSOR%NOTFOUND;
final_t(1).v_rec:='';
final_t(1).v_rec:=final_t(1).v_rec||v_sep|| ename1;
Final_t(1).v_rec:=final_t(1).v_rec||v_sep|| last_update1;
Final_t(1).v_rec:=final_t(1).v_rec||v_sep|| dname1;
Final_t(1).v_rec:=final_t(1).v_rec||v_sep|| last_update2;
Final_t(1).v_rec:=final_t(1).v_rec||v_sep|| city1;
Final_t(1).v_rec:=final_t(1).v_rec||v_sep|| last_update3;
Uii_File_Pack_kkk1.p_write_rec_kkk1(final_t, v_out_file, v_record_count);
final_t.DELETE();
END LOOP;
--utl_file.fclose(v_out_file);
Uii_File_Pack_kkk1.p_write_footer_kkk1(v_out_file,v_record_count);
END batch_extract_kkk11;
END batch_kkk11;
/
CREATE OR REPLACE PACKAGE Uii_File_Pack_kkk1
IS
TYPE rec_body IS RECORD(
v_rec varchar2(5000)
);
TYPE tbl_body IS TABLE OF rec_body INDEX BY BINARY_INTEGER;
PROCEDURE p_write_header_kkk1
(p_dir_name IN VARCHAR2,
p_file_name IN VARCHAR2,
p_out_file IN OUT utl_file.file_type
);
PROCEDURE p_write_rec_kkk1 (p_rec IN tbl_body,
p_out_file IN utl_file.file_type,
p_record_count IN out NUMBER
);
PROCEDURE p_write_footer_kkk1 (p_out_file IN out utl_file.file_type,
p_record_count IN NUMBER
);
END;
/
CREATE OR REPLACE PACKAGE BODY Uii_File_Pack_kkk1 IS
PROCEDURE p_write_header_kkk1
(p_dir_name IN VARCHAR2,
p_file_name IN VARCHAR2,
p_out_file IN OUT utl_file.file_type
)
IS
separator VARCHAR2(2) :=chr(199);
v_header_record varchar2(2000);
p_rec_type1 VARCHAR2(20):='01';
BEGIN
p_out_file:=utl_file.fopen(p_dir_name,p_file_name,'w');
v_header_record :=p_rec_type1||separator||TO_CHAR(systimestamp,'YYYYMMDDHH24MISS')||separator;
utl_file.putf(p_out_file,v_header_record);
END p_write_header_kkk1;
PROCEDURE p_write_rec_kkk1 (p_rec IN tbl_body,
p_out_file IN utl_file.file_type,
p_record_count IN out NUMBER )
IS
v_record VARCHAR2(2000);
separator VARCHAR2(2) :=chr(199);
p_rec_type2 number(10):=10;
BEGIN
IF p_rec.COUNT() >0 THEN
FOR i IN p_rec.FIRST..p_rec.LAST
LOOP
v_record := p_rec_type2||p_rec(i).v_rec;
utl_file.putf(p_out_file,v_record);
END LOOP;
END IF;
p_record_count := p_record_count +p_rec.COUNT() ;
END p_write_rec_kkk1;
PROCEDURE p_write_footer_kkk1 (p_out_file IN out utl_file.file_type,
p_record_count IN NUMBER
) IS
v_footer_record VARCHAR2(2000);
p_rec_type number(10):=99;
separator VARCHAR2(2) :=chr(199);
BEGIN
v_footer_record := p_rec_type||separator||LPAD(p_record_count,10,'0');
utl_file.putf(p_out_file, v_footer_record);
utl_file.fclose (p_out_file);
END p_write_footer_kkk1;
END Uii_File_Pack_kkk1;
/