SQL & PL/SQL

Hi all,

I have a small procedure that needs some performance enhancement to reduce the execution time. It is quite a simple procedure that goes through two tables (Table A and Table B). For every record in Table A, it writes the record to the file, goes and finds all the corresponding records in Table B (Table A.test_id = Table B.test_id) and writes those records as well, goes on to the next record in Table A, writes it, finds matches in Table B, writes them, and so on. It works just fine, but does take a while to complete if there is a huge number of records in both the tables. Is there anyway to tune the proc to make it run faster, with a forall or something? Any input would be really helpful.

Thanks!

CREATE OR REPLACE PROCEDURE PROC_MERGE_RECS
IS
CURSOR get_header_cur is
select * from Table A;
header_sum_data get_header_cur%rowtype;
v_writefile UTL_FILE.file_type;
v_sum_buffer_header varchar2(3000);
v_sum_buffer_detail varchar2(3000);

BEGIN

v_writefile:= UTL_FILE.fopen('OUTPUT_DIR', 'test.txt','w',32767);

open get_header_cur;
loop
fetch get_header_cur into header_sum_data;
exit when get_header_cur%notfound;

v_sum_buffer_header := header_sum_data.REC_TYPE_01||header_sum_data.MEMBER_ID

UTL_FILE.PUT_LINE(v_writefile,v_sum_buffer_header);--Write the record from Table A
for detail_rec in (select * from Table B where test_id = header_sum_data.test_id)--Find the corresponding records in Tables B
loop
v_sum_buffer_detail := detail_rec.REC_TYPE_02|| detail_rec.MRD_ID || detail_rec.ATH_ID
UTL_FILE.PUT_LINE(v_writefile, v_sum_buffer_detail);--Write the corresponding records from Table B

end loop; --Close Table B loop

end loop;--Close Table A loop
UTL_FILE.FCLOSE(v_writefile);

EXCEPTION
WHEN others
THEN dbms_output.put_line('Extract:'||SQLERRM);
END;
/