Home » Other » Test » comparing files based on timestamp
comparing files based on timestamp [message #165129] Tue, 28 March 2006 07:23 Go to next message
kodali
Messages: 24
Registered: February 2006
Location: PUNE
Junior Member


Hi,

Here i am giving packages and i want to compare files based on timestamp.everyday i am producing full flat file.i want to compare today's and yesterday's file if there is any comparison of that file i want to send it to that difference file to onsite.

Here i have given only three tables. for these tables having common column for last_update.if it is three columns it is working.if it has more than 100 columns and 1 million of records this logic is not possible. if anybody face like this problem and solved to this type of problem.let me know.please send sample code asap.








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;
/

Re: comparing files based on timestamp [message #165182 is a reply to message #165129] Tue, 28 March 2006 18:20 Go to previous message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Posted elsewhere.

David
Previous Topic: how can i provide link and mask url?
Next Topic: hello every body test message
Goto Forum:
  


Current Time: Mon Jan 25 15:11:12 CST 2021