Home » RDBMS Server » Performance Tuning » Procedures not getting executed (Oracle 9.2.0.8 (HP-UX))
| Procedures not getting executed [message #475179] |
Mon, 13 September 2010 08:34  |
indrajit2002
Messages: 53
Registered: November 2007
Location: INDIA
|
Member |
|
|
Hi,
I have two procedures which gets running for ever and do not execute.I am totally clueless about what could be the issue.Please help.
The two procedures are,
1. CREATE OR REPLACE procedure MDM_DBA.upd_icd_stats_with_mip as
v_icd_group ees_icd_9_group.icd_group%type;
cursor cur_icd_groups is
select distinct icd_group from ees_icd_9_group
order by icd_group;
begin
update ees_icd_stats e
set mip_yn = 'Y'
where
mip_yn is null
and icd_code in (select icd_code from ees_icd_9_code where mip_yn = 'Y');
commit;
open cur_icd_groups;
loop
fetch cur_icd_groups into v_icd_group;
exit when cur_icd_groups%NOTFOUND;
update ees_icd_stats e
set mip_yn = 'Y'
where
icd_code like v_icd_group || '%'
and icd_code not in (select icd_code from ees_icd_9_code where mip_yn = 'Y')
and exists (select 1 from ees_charge_master c where c.pdct_cat = 'Trocar' and e.pat_key = c.pat_key)
and mip_yn is null;
commit;
update ees_icd_stats e
set mip_yn = 'N'
where
icd_code like v_icd_group || '%'
and icd_code not in (select icd_code from ees_icd_9_code where mip_yn = 'Y')
and mip_yn is null;
commit;
end loop;
close cur_icd_groups;
end;
/
2. CREATE OR REPLACE procedure MDM_DBA.upd_energy_master_with_mip as
v_pat_key ees_energy_master.pat_key%type;
v_icd_code ees_energy_master.icd_code%type;
lap_charges number;
v_mip_yn ees_energy_master.mip_yn%type;
cursor cur_energy_master is
select distinct pat_key, icd_code from ees_energy_master where
icd_code not in ('68.51', '68.31', '68.41', '68.61', '68.71', '45.81', '48.51', '48.42', '17.31', '17.32',
'17.33', '17.34', '17.35', '17.36', '17.39', '32.41', '32.30', '32.50', '32.20', '32.25', '32.28', '33.27')
and mip_yn is null;
begin
open cur_energy_master;
loop
fetch cur_energy_master into v_pat_key, v_icd_code;
exit when cur_energy_master%NOTFOUND;
select count(1) into lap_charges
from ees_charge_master ch
where
ch.pat_key = v_pat_key
and ch.pdct_cat = 'Trocar';
if lap_charges > 0
then
v_mip_yn := 'Y';
else
v_mip_yn := 'N';
end if;
update ees_energy_master
set mip_yn = v_mip_yn
where pat_key = v_pat_key
and icd_code = v_icd_code;
end loop;
close cur_energy_master;
update ees_energy_master
set mip_yn = 'Y'
where icd_code in ('68.51', '68.31', '68.41', '68.61', '68.71', '45.81', '48.51', '48.42',
'17.31', '17.32', '17.33', '17.34', '17.35', '17.36', '17.39', '32.41', '32.30', '32.50', '32.20', '32.25', '32.28', '33.27');
commit;
end;
/
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun May 05 10:00:00 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
