I have a plsql procedure which selects rows from one table and inserts into other few tables. As it has more than lacs rows I made it restartable, means if it gets any error which causes for stoping the procedure, I dont have to start it again from record one. It can go on from the record where it was stoped.
follwing is the script. --> genreceipt.sql
l_prog varchar2(100) := 'RCPT_GEN';
cursor c1 is
select distinct loan_no from loan_details
where loan_no > (select last_txnno from receipts_log where program = l_prog)
order by loan_no;
FOR x in c1 loop
receipts_generate(x.loan_no); --This proc inserts rows into many tables
update receipts_log -- LOG
set last_txnno = x.loan_no,
count_rows = count_rows+1
where program = l_prog
when others then
errm := sqlerrm;
insert into error_loans values
(x.loan_no, errm, l_prog, sysdate);
now i started this script in one telnet session -->
and in another session say TOAD window i'm monitoring receipts_log -->
select * from receipts_log where program ='RCPT_GEN';
program last_txnno count_rows
------- ---------- -----------
RCPT_GEN LAABD09824 50
here last_txnno is always updated with last loan no generated and count_rows having total loans generated.
Now Problem is .....
I want to stop that procedure manually so i pressed ctrl C it didnt worked. so I stoped that procedure by clicking disconnect which closed my telnet window. but it dint stop my procedure. in another session my count_rows and last_txnno in is still showing that my loans are getting generated. Then I killed my session, but no use. my counter stoped only when all loans got generated.
IS THERE ANY WAY TO STOP THAT SESSION. WHICH SHOWS AS IT IS KILLED IN MY SESSION LOG. I FEEL IT IS RUNNING IN BACKGROUND.