Home » RDBMS Server » Performance Tuning » Database stops responding when run on production server (Oracle 10g)
| Database stops responding when run on production server [message #408172] |
Mon, 15 June 2009 00:40  |
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
|
Member |
|
|
Hi,
The below procedure is used to display data in a calendar used to show appointment details.
The procedure runs fine and fast when run on test database where data is not much.
But when it run on Production database, it started consuming memory very fast and database was restarted because of it.
I am sorry to paste such a long code here but only to know where I am going wrong. Are the cursors creating problem? I have closed them after fetching data from them.
I can't test them on prodution server anymore before finding the cause. Since it run fast on UAT database, I am not able to understand where the problem is.
Please look into the code and advice as where I am going wrong.
CREATE OR REPLACE PROCEDURE DM_CALENDAR_DATA_PROC(v_seq_id in NUMBER,next_previous_date in VARCHAR2,
next_previous_click in VARCHAR2, v_cal_form_id in varchar2,v_lead_status in varchar2, v_fsc_code in varchar2, app_current_flag in varchar2,opp_id_current in varchar2, opp_id_colour in varchar2,
cur_calendarbody_out out TYPES.cursor_type, v_cur_date out DATE, max_overlap_limit out VARCHAR2)
IS
v_cal_form_url VARCHAR(2000);
v_cal_start_time VARCHAR(50);
v_cal_start_hr VARCHAR(50);
v_cal_start_min VARCHAR(50);
v_cal_end_time VARCHAR(50);
v_cal_default_interval VARCHAR(50);
v_cal_default_interval_hr VARCHAR(50);
v_cal_default_interval_mn VARCHAR(50);
v_cal_default_overlap VARCHAR(50);
v_cal_default_overlap_hr VARCHAR(50);
v_cal_default_overlap_mn VARCHAR(50);
v_cal_app_date_column VARCHAR(50);
v_cal_app_date_order VARCHAR(50);
v_cal_url_indx NUMBER := 0;
v_cal_start_s VARCHAR(50);
v_cal_start_e VARCHAR(50);
v_app_current_date VARCHAR(50);
v_app_current_flag VARCHAR(50);
v_cal_std_interval_hr VARCHAR(50) := '00';
v_cal_std_interval_mn VARCHAR(50):= '30';
v_cal_int_chk_time VARCHAR(50);
v_cal_int_st_time VARCHAR(50);
v_cal_int_time VARCHAR(50);
v_cal_overlap VARCHAR(50);
v_cal_overlap_hr VARCHAR(50);
v_cal_overlap_mn VARCHAR(50);
v_cal_overlap_ampm VARCHAR(50);
v_cal_overlap_limit NUMBER := 0;
v_cur_opp_id VARCHAR(100);
v_cur_fsc_name VARCHAR(100);
v_cur_opp_date VARCHAR(100);
v_cur_opp_time VARCHAR(100);
v_cur_cust_name VARCHAR(100);
var_time_wise_count NUMBER := 0;
var_data_counter NUMBER := 0;
var_data_globle_counter NUMBER := 0;
v_max_overlap_limit NUMBER := 0;
v_column_colour VARCHAR(50);
v_default_colour VARCHAR(50);
var_row_id NUMBER := 0 ;
var_interval_flag VARCHAR(50) := 'N';
var_param_string VARCHAR(9000);
var_param_colur_string VARCHAR(9000);
var_param_final_string VARCHAR(9000);
var_param_final_colur_string VARCHAR(9000);
var_param_query_string VARCHAR(5000);
var_param_query_string_value VARCHAR(5000);
var_tb_column_count NUMBER := 40;
var_cust_data_count NUMBER := 0;
var_last_row_id NUMBER := 0 ;
var_temp_row_id NUMBER := 0;
var_temp_incr_id NUMBER := 0;
var_fsc_data varchar2(50);
var_chk_previous_time varchar2(50);
var_incr Number := 0;
var_temp Number := 0;
var_select_table_name varchar2(100);
var_select_optional_name varchar2(100);
var_select_existing_name varchar2(100);
p_seq_id NUMBER := v_seq_id;
TYPE coll_opp_id IS TABLE OF VARCHAR2(500);
TYPE coll_fsc_name IS TABLE OF VARCHAR2(500);
TYPE coll_opp_date IS TABLE OF VARCHAR2(500);
TYPE coll_opp_time IS TABLE OF VARCHAR2(500);
TYPE coll_cust_name IS TABLE OF VARCHAR2(500);
TYPE coll_param_list IS TABLE OF VARCHAR2(500);
TYPE coll_param_colour_list IS TABLE OF VARCHAR2(500);
TYPE coll_int_flag_list IS TABLE OF VARCHAR2(500);
collectn_opp_id coll_opp_id :=coll_opp_id();
collectn_fsc_name coll_fsc_name :=coll_fsc_name();
collectn_opp_date coll_opp_date :=coll_opp_date();
collectn_opp_time coll_opp_time :=coll_opp_time();
collectn_cust_name coll_cust_name :=coll_cust_name();
collectn_int_flag_list coll_int_flag_list :=coll_int_flag_list();
collectn_param_list coll_param_list :=coll_param_list();
collectn_param_colour_list coll_param_colour_list :=coll_param_colour_list();
--data_anhfsc_link
CURSOR data_anhfsc_link(leadstatus varchar2, fsccode varchar2, curdate varchar2, asc_desc_order varchar2, current_opp_id varchar2)
IS
Select ab.opp_id, ab.fsc_code, ab.app_date, ab.app_time, ab.customer_name from (
Select a.FSC_OPPORTUNITY_ID opp_id, a.ANH_FSC_CODE fsc_code,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE) , 'dd-Mon-yyyy HH:MI AM') app_date,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE) , 'HH:MI AM') app_time,
(b.FIRST_NAME||' '||b.MIDDLE_NAME||' '||b.LAST_NAME) customer_name
from dm_opportunity_details_fsc a, dm_customer_details b where a.lead_status=leadstatus
and a.anh_fsc_code = fsccode and a.customer_id = b.customer_id
and trunc(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE))= curdate
UNION ALL
Select a.FSC_OPPORTUNITY_ID opp_id, fsccode fsc_code,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE) , 'dd-MON-YYYY HH:MI AM') app_date,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE) , 'HH:MI AM') app_time,
(b.FIRST_NAME||' '||b.MIDDLE_NAME||' '||b.LAST_NAME) customer_name
from dm_opportunity_details_fsc a, dm_customer_details b where a.lead_status=leadstatus
and a.anh_fsc_code is null and a.fsc_opportunity_id = current_opp_id and a.customer_id = b.customer_id
and trunc(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE))= curdate
)ab order by ab.app_date||' '||asc_desc_order ;
--data_anhfsc_menu
CURSOR data_anhfsc_menu_asc(leadstatus varchar2, fsccode varchar2, curdate varchar2)
IS
Select a.FSC_OPPORTUNITY_ID opp_id, a.ANH_FSC_CODE fsc_code,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE), 'dd-MON-YYYY HH:MI AM') app_date,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE) , 'HH:MI AM') app_time,
(b.FIRST_NAME||' '||b.MIDDLE_NAME||' '||b.LAST_NAME) customer_name
from dm_opportunity_details_fsc a, dm_customer_details b where a.lead_status=leadstatus
and a.anh_fsc_code = fsccode and a.customer_id = b.customer_id
and trunc(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE))= curdate
order by decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE) ASC;
CURSOR data_anhfsc_menu_desc(leadstatus varchar2, fsccode varchar2, curdate varchar2)
IS
Select a.FSC_OPPORTUNITY_ID opp_id, a.ANH_FSC_CODE fsc_code,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE), 'dd-MON-YYYY HH:MI AM') app_date,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE) , 'HH:MI AM') app_time,
(b.FIRST_NAME||' '||b.MIDDLE_NAME||' '||b.LAST_NAME) customer_name
from dm_opportunity_details_fsc a, dm_customer_details b where a.lead_status=leadstatus
and a.anh_fsc_code = fsccode and a.customer_id = b.customer_id
and trunc(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE))= curdate
order by decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE) DESC;
--data_datestage3_link
CURSOR data_datestage3_link(leadstatus varchar2, fsccode varchar2, curdate varchar2, asc_desc_order varchar2, current_opp_id varchar2)
IS
Select ab.opp_id, ab.fsc_code, ab.app_date, ab.app_time, ab.customer_name from (
Select a.FSC_OPPORTUNITY_ID opp_id, a.ANH_FSC_CODE fsc_code,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE) , 'dd-Mon-yyyy HH:MI AM') app_date,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE) , 'HH:MI AM') app_time,
(b.FIRST_NAME||' '||b.MIDDLE_NAME||' '||b.LAST_NAME) customer_name
from dm_opportunity_details_fsc a, dm_customer_details b where a.lead_status=leadstatus
and a.anh_fsc_code = fsccode and a.customer_id = b.customer_id
and trunc(decode(DATE_STAGE_3, null, DATE_STAGE_2,DATE_STAGE_3))= curdate
UNION ALL
Select a.FSC_OPPORTUNITY_ID opp_id, fsccode fsc_code,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE) , 'dd-MON-YYYY HH:MI AM') app_date,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE) , 'HH:MI AM') app_time,
(b.FIRST_NAME||' '||b.MIDDLE_NAME||' '||b.LAST_NAME) customer_name
from dm_opportunity_details_fsc a, dm_customer_details b where a.lead_status=leadstatus
and a.anh_fsc_code is null and a.fsc_opportunity_id = current_opp_id and a.customer_id = b.customer_id
and trunc(decode(DATE_STAGE_3, null, DATE_STAGE_2,DATE_STAGE_3))= curdate
)ab order by ab.app_date||' '||asc_desc_order ;
--data_datestage3_menu
CURSOR data_datestage3_menu_asc(leadstatus varchar2, fsccode varchar2, curdate varchar2)
IS
Select a.FSC_OPPORTUNITY_ID opp_id, a.ANH_FSC_CODE fsc_code,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE), 'dd-MON-YYYY HH:MI AM') app_date,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE) , 'HH:MI AM') app_time,
(b.FIRST_NAME||' '||b.MIDDLE_NAME||' '||b.LAST_NAME) customer_name
from dm_opportunity_details_fsc a, dm_customer_details b where a.lead_status=leadstatus
and a.anh_fsc_code = fsccode and a.customer_id = b.customer_id
and trunc(decode(DATE_STAGE_3, null, DATE_STAGE_2,DATE_STAGE_3))= v_cur_date
order by decode(DATE_STAGE_3, null, DATE_STAGE_2,DATE_STAGE_3) ASC;
CURSOR data_datestage3_menu_desc(leadstatus varchar2, fsccode varchar2, curdate varchar2)
IS
Select a.FSC_OPPORTUNITY_ID opp_id, a.ANH_FSC_CODE fsc_code,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE), 'dd-MON-YYYY HH:MI AM') app_date,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE) , 'HH:MI AM') app_time,
(b.FIRST_NAME||' '||b.MIDDLE_NAME||' '||b.LAST_NAME) customer_name
from dm_opportunity_details_fsc a, dm_customer_details b where a.lead_status=leadstatus
and a.anh_fsc_code = fsccode and a.customer_id = b.customer_id
and trunc(decode(DATE_STAGE_3, null, DATE_STAGE_2,DATE_STAGE_3))= v_cur_date
order by decode(DATE_STAGE_3, null, DATE_STAGE_2,DATE_STAGE_3) DESC;
--data_oppdate_link
CURSOR data_oppdate_link(leadstatus varchar2, fsccode varchar2, curdate varchar2, asc_desc_order varchar2, current_opp_id varchar2)
IS
Select ab.opp_id, ab.fsc_code, ab.app_date, ab.app_time, ab.customer_name from (
Select a.FSC_OPPORTUNITY_ID opp_id, a.ANH_FSC_CODE fsc_code,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE) , 'dd-Mon-yyyy HH:MI AM') app_date,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE) , 'HH:MI AM') app_time,
(b.FIRST_NAME||' '||b.MIDDLE_NAME||' '||b.LAST_NAME) customer_name
from dm_opportunity_details_fsc a, dm_customer_details b where a.lead_status=leadstatus
and a.anh_fsc_code = fsccode and a.customer_id = b.customer_id
and trunc(OPPORTUNITY_DATE)= curdate
UNION ALL
Select a.FSC_OPPORTUNITY_ID opp_id, fsccode fsc_code,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE) , 'dd-MON-YYYY HH:MI AM') app_date,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE) , 'HH:MI AM') app_time,
(b.FIRST_NAME||' '||b.MIDDLE_NAME||' '||b.LAST_NAME) customer_name
from dm_opportunity_details_fsc a, dm_customer_details b where a.lead_status=leadstatus
and a.anh_fsc_code is null and a.fsc_opportunity_id = current_opp_id and a.customer_id = b.customer_id
and trunc(OPPORTUNITY_DATE)= curdate
)ab order by ab.app_date||' '||asc_desc_order ;
--data_oppdate_menu
CURSOR data_oppdate_menu_asc(leadstatus varchar2, fsccode varchar2, curdate varchar2)
IS
Select a.FSC_OPPORTUNITY_ID opp_id, a.ANH_FSC_CODE fsc_code,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE), 'dd-MON-YYYY HH:MI AM') app_date,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE) , 'HH:MI AM') app_time,
(b.FIRST_NAME||' '||b.MIDDLE_NAME||' '||b.LAST_NAME) customer_name
from dm_opportunity_details_fsc a, dm_customer_details b where a.lead_status=leadstatus
and a.anh_fsc_code = fsccode and a.customer_id = b.customer_id
and trunc(OPPORTUNITY_DATE)= v_cur_date
order by OPPORTUNITY_DATE ASC;
CURSOR data_oppdate_menu_desc(leadstatus varchar2, fsccode varchar2, curdate varchar2)
IS
Select a.FSC_OPPORTUNITY_ID opp_id, a.ANH_FSC_CODE fsc_code,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE), 'dd-MON-YYYY HH:MI AM') app_date,
to_char(decode(ANH_FSC_APPT_FIXED_DATE, null, TELECALLER_APPT_FIXED_DATE,ANH_FSC_APPT_FIXED_DATE) , 'HH:MI AM') app_time,
(b.FIRST_NAME||' '||b.MIDDLE_NAME||' '||b.LAST_NAME) customer_name
from dm_opportunity_details_fsc a, dm_customer_details b where a.lead_status=leadstatus
and a.anh_fsc_code = fsccode and a.customer_id = b.customer_id
and trunc(OPPORTUNITY_DATE)= v_cur_date
order by OPPORTUNITY_DATE DESC;
BEGIN
delete from DM_CALENDAR_TEMP_TABLE;
commit;
v_default_colour := opp_id_colour;
v_app_current_flag := app_current_flag;
var_temp_row_id := 0;
var_last_row_id := 0;
begin
Select a.user_id, f.overlap_time, f.overlap_hour, f.overlap_min,
f.overlap_ampm, f.overlap_limit into var_fsc_data, v_cal_overlap,v_cal_overlap_hr,
v_cal_overlap_mn, v_cal_overlap_ampm, v_cal_overlap_limit
from adm_users a, adm_profile_master b, adm_profile_cluster c, adm_cluster_master d,
adm_calendar_cluster_mapping e, adm_calendar_overlap_mapping f
where a.user_id = b.user_id
and a.is_active=b.is_active
and a.is_active = 1
and a.user_id = v_fsc_code
and b.profile_id = c.profile_id
and b.operation_id=1
and c.cluster_id = d.cluster_id
and b.operation_id=d.operation_type_id
and d.cluster_id = e.cluster_id
and e.mapping_id=f.mapping_id
and d.operation_type_id=f.operation_type_id;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('Exception wile selecting User Data');
end;
begin
if v_cal_form_id is not null OR v_cal_form_id <> 'NULL'
then
Select FORM_URL into v_cal_form_url from ADM_SAVED_FORM where FORM_ID = v_cal_form_id;
else
Select e.FORM_URL into v_cal_form_url
from
adm_users a,
adm_profile_master b,
adm_user_group c,
adm_form_privileges d,
adm_saved_form e
where
a.user_id=b.user_id
and a.is_active=b.is_active
and a.is_active=1
and a.user_id=v_fsc_code
and b.user_id=c.user_id
and c.group_id=d.group_id
and d.form_id = e.form_id
and b.operation_id=e.operation_type_id
and e.operation_type_id = 1
and upper(e.form_url) like upper('/fscCalendarNew.do%');
end if;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('Exception wile selecting User Data');
end;
v_cur_date := TO_DATE(next_previous_date, 'dd-mon-yyyy'); -- replaced and put here on 12/06/09
IF v_cal_form_url IS NULL OR v_cal_form_url = '' THEN -- added the if condition on 12/06/09 to avoid looping in case data is not found
v_flag := 0;
ELSE
v_cal_form_url:= replace(v_cal_form_url,'/fscCalendarNew.do?','');
v_cal_form_url:= replace(v_cal_form_url,'methodshowFSCList','');
v_cal_form_url:= replace(v_cal_form_url,'=','');
v_cal_form_url:= replace(v_cal_form_url,'&','');
v_cal_start_hr := substr(v_cal_form_url,to_number(instr(v_cal_form_url,'stHr')+4),to_number(to_number(instr(v_cal_form_url,'overlapHr'))-to_number(instr(v_cal_form_url,'stHr')+4)));
if length(v_cal_start_hr) = 1 then
v_cal_start_hr := '0' || v_cal_start_hr;
end if;
v_cal_start_min := substr(v_cal_form_url,to_number(instr(v_cal_form_url,'stMin')+5),to_number(to_number(instr(v_cal_form_url,'overlapLimit'))-to_number(instr(v_cal_form_url,'stMin')+5)));
if length(v_cal_start_min) = 1 then
v_cal_start_min := v_cal_start_min || '0';
end if;
v_cal_start_time:= v_cal_start_hr || ':' || v_cal_start_min || ' ' || substr(v_cal_form_url,to_number(instr(v_cal_form_url,'stimeAmPm')+9),to_number(to_number(instr(v_cal_form_url,'timeintervalMin'))-to_number(instr(v_cal_form_url,'stimeAmPm')+9)));
v_cal_end_time:= substr(v_cal_form_url,to_number(instr(v_cal_form_url,'etHr')+4),to_number(to_number(instr(v_cal_form_url,'timeintervalHr'))-to_number(instr(v_cal_form_url,'etHr')+4)))||':'||substr(v_cal_form_url,to_number(instr(v_cal_form_url,'etMin')+5),to_number(to_number(instr(v_cal_form_url,'dateForOverlap'))-to_number(instr(v_cal_form_url,'etMin')+5)))||' '||substr(v_cal_form_url,to_number(instr(v_cal_form_url,'etimeAmPm')+9),to_number(to_number(instr(v_cal_form_url,'etHr'))-to_number(instr(v_cal_form_url,'etimeAmPm')+9)));
v_cal_default_interval:= substr(v_cal_form_url,to_number(instr(v_cal_form_url,'timeintervalHr')+14),to_number(to_number(instr(v_cal_form_url,'stHr'))-to_number(instr(v_cal_form_url,'timeintervalHr')+14)))||':'||substr(v_cal_form_url,to_number(instr(v_cal_form_url,'timeintervalMin')+15),to_number(to_number(instr(v_cal_form_url,'stMin'))-to_number(instr(v_cal_form_url,'timeintervalMin')+15)));
v_cal_default_interval_hr:= substr(v_cal_form_url,to_number(instr(v_cal_form_url,'timeintervalHr')+14),to_number(to_number(instr(v_cal_form_url,'stHr'))-to_number(instr(v_cal_form_url,'timeintervalHr')+14)));
v_cal_default_interval_mn:= substr(v_cal_form_url,to_number(instr(v_cal_form_url,'timeintervalMin')+15),to_number(to_number(instr(v_cal_form_url,'stMin'))-to_number(instr(v_cal_form_url,'timeintervalMin')+15)));
v_column_colour := substr(v_cal_form_url,to_number(instr(v_cal_form_url,'hidEColor')+9),to_number(to_number(instr(v_cal_form_url,'target'))-to_number(instr(v_cal_form_url,'hidEColor')+9)));
v_cal_app_date_column:= substr(v_cal_form_url,to_number(instr(v_cal_form_url,'dateForOverlap')+14),to_number(to_number(instr(v_cal_form_url,'displayRoleId'))-to_number(instr(v_cal_form_url,'dateForOverlap')+14)));
v_cal_app_date_order:= upper(substr(v_cal_form_url,to_number(instr(v_cal_form_url,'orderBy')+7),to_number(to_number(instr(v_cal_form_url,'etMin'))-to_number(instr(v_cal_form_url,'orderBy')+7))));
if var_fsc_data is null
then
v_cal_default_overlap:= substr(v_cal_form_url,to_number(instr(v_cal_form_url,'overlapHr')+9),to_number(to_number(instr(v_cal_form_url,'overlapMin'))-to_number(instr(v_cal_form_url,'overlapHr')+9)))||':'||substr(v_cal_form_url,to_number(instr(v_cal_form_url,'overlapMin')+10),to_number(to_number(instr(v_cal_form_url,'orderBy'))-to_number(instr(v_cal_form_url,'overlapMin')+10)));
v_cal_default_overlap_hr:= substr(v_cal_form_url,to_number(instr(v_cal_form_url,'overlapHr')+9),to_number(to_number(instr(v_cal_form_url,'overlapMin'))-to_number(instr(v_cal_form_url,'overlapHr')+9)));
v_cal_default_overlap_mn:= substr(v_cal_form_url,to_number(instr(v_cal_form_url,'overlapMin')+10),to_number(to_number(instr(v_cal_form_url,'orderBy'))-to_number(instr(v_cal_form_url,'overlapMin')+10)));
v_max_overlap_limit := TO_NUMBER(substr(v_cal_form_url,to_number(instr(v_cal_form_url,'overlapLimit')+12),to_number(to_number(instr(v_cal_form_url,'etimeAmPm'))-to_number(instr(v_cal_form_url,'overlapLimit')+12))));
else
v_cal_default_overlap:= v_cal_overlap;
v_cal_default_overlap_hr:= v_cal_overlap_hr;
v_cal_default_overlap_mn:= v_cal_overlap_mn;
v_max_overlap_limit := v_cal_overlap_limit;
end if;
IF next_previous_click is not null and next_previous_click = 'N' THEN
delete from DM_CALENDAR_TEMP_TABLE;
commit;
v_cur_date := (TO_DATE(next_previous_date, 'dd-Mon-yyyy') + 1);
END IF;
IF next_previous_click is not null and next_previous_click = 'P' THEN
delete from DM_CALENDAR_TEMP_TABLE;
commit;
v_cur_date := (TO_DATE(next_previous_date, 'dd-Mon-yyyy') - 1);
END IF;
var_select_table_name :=v_cal_app_date_column ;
if var_select_table_name = 'ANH_FSC_APPT_FIXED_DATE'
then
if v_app_current_flag = 'Y'
then
open data_anhfsc_link(v_lead_status, v_fsc_code, v_cur_date,v_cal_app_date_order,opp_id_current);
else
if UPPER(v_cal_app_date_order) = 'ASC'
then
open data_anhfsc_menu_asc(v_lead_status, v_fsc_code, v_cur_date);
else
open data_anhfsc_menu_desc(v_lead_status, v_fsc_code, v_cur_date);
end if;
end if;
end if;
if var_select_table_name = 'DATE_STAGE_3'
then
if v_app_current_flag = 'Y'
then
open data_datestage3_link(v_lead_status, v_fsc_code, v_cur_date,v_cal_app_date_order,opp_id_current);
else
if UPPER(v_cal_app_date_order) = 'ASC'
then
open data_datestage3_menu_asc(v_lead_status, v_fsc_code, v_cur_date);
else
open data_datestage3_menu_desc(v_lead_status, v_fsc_code, v_cur_date);
end if;
end if;
end if;
if var_select_table_name = 'OPPORTUNITY_DATE'
then
if v_app_current_flag = 'Y'
then
open data_oppdate_link(v_lead_status, v_fsc_code, v_cur_date,v_cal_app_date_order,opp_id_current);
else
if UPPER(v_cal_app_date_order) = 'ASC'
then
open data_oppdate_menu_asc(v_lead_status, v_fsc_code, v_cur_date);
else
open data_oppdate_menu_desc(v_lead_status, v_fsc_code, v_cur_date);
end if;
end if;
end if;
loop
if var_select_table_name = 'ANH_FSC_APPT_FIXED_DATE'
then
if v_app_current_flag = 'Y'
then
FETCH data_anhfsc_link INTO v_cur_opp_id, v_cur_fsc_name,v_cur_opp_date,v_cur_opp_time,v_cur_cust_name ;
EXIT WHEN data_anhfsc_link%NOTFOUND;
else
if UPPER(v_cal_app_date_order) = 'ASC'
then
FETCH data_anhfsc_menu_asc INTO v_cur_opp_id, v_cur_fsc_name,v_cur_opp_date,v_cur_opp_time,v_cur_cust_name ;
EXIT WHEN data_anhfsc_menu_asc%NOTFOUND;
else
FETCH data_anhfsc_menu_desc INTO v_cur_opp_id, v_cur_fsc_name,v_cur_opp_date,v_cur_opp_time,v_cur_cust_name ;
EXIT WHEN data_anhfsc_menu_desc%NOTFOUND;
end if;
end if;
end if;
if var_select_table_name = 'DATE_STAGE_3'
then
if v_app_current_flag = 'Y'
then
FETCH data_datestage3_link INTO v_cur_opp_id, v_cur_fsc_name,v_cur_opp_date,v_cur_opp_time,v_cur_cust_name ;
EXIT WHEN data_datestage3_link%NOTFOUND;
else
if UPPER(v_cal_app_date_order) = 'ASC'
then
FETCH data_datestage3_menu_asc INTO v_cur_opp_id, v_cur_fsc_name,v_cur_opp_date,v_cur_opp_time,v_cur_cust_name ;
EXIT WHEN data_datestage3_menu_asc%NOTFOUND;
else
FETCH data_datestage3_menu_desc INTO v_cur_opp_id, v_cur_fsc_name,v_cur_opp_date,v_cur_opp_time,v_cur_cust_name ;
EXIT WHEN data_datestage3_menu_desc%NOTFOUND;
end if;
end if;
end if;
if var_select_table_name = 'OPPORTUNITY_DATE'
then
if v_app_current_flag = 'Y'
then
FETCH data_oppdate_link INTO v_cur_opp_id, v_cur_fsc_name,v_cur_opp_date,v_cur_opp_time,v_cur_cust_name ;
EXIT WHEN data_oppdate_link%NOTFOUND;
else
if UPPER(v_cal_app_date_order) = 'ASC'
then
FETCH data_oppdate_menu_asc INTO v_cur_opp_id, v_cur_fsc_name,v_cur_opp_date,v_cur_opp_time,v_cur_cust_name ;
EXIT WHEN data_oppdate_menu_asc%NOTFOUND;
else
FETCH data_oppdate_menu_desc INTO v_cur_opp_id, v_cur_fsc_name,v_cur_opp_date,v_cur_opp_time,v_cur_cust_name ;
EXIT WHEN data_oppdate_menu_desc%NOTFOUND;
end if;
end if;
end if;
collectn_opp_id.extend;
collectn_opp_id(collectn_opp_id.last) :=v_cur_opp_id ;
collectn_fsc_name.extend;
collectn_fsc_name(collectn_fsc_name.last) :=v_cur_fsc_name ;
collectn_opp_date.extend;
collectn_opp_date(collectn_opp_date.last) :=v_cur_opp_date ;
collectn_opp_time.extend;
collectn_opp_time(collectn_opp_time.last) :=v_cur_opp_time ;
collectn_cust_name.extend;
collectn_cust_name(collectn_cust_name.last) :=v_cur_cust_name ;
end loop;
if var_select_table_name = 'ANH_FSC_APPT_FIXED_DATE'
then
if v_app_current_flag = 'Y'
then
close data_anhfsc_link;
else
if UPPER(v_cal_app_date_order) = 'ASC'
then
close data_anhfsc_menu_asc;
else
close data_anhfsc_menu_desc;
end if;
end if;
end if;
if var_select_table_name = 'DATE_STAGE_3'
then
if v_app_current_flag = 'Y'
then
close data_datestage3_link;
else
if UPPER(v_cal_app_date_order) = 'ASC'
then
close data_datestage3_menu_asc;
else
close data_datestage3_menu_desc;
end if;
end if;
end if;
if var_select_table_name = 'OPPORTUNITY_DATE'
then
if v_app_current_flag = 'Y'
then
close data_oppdate_link;
else
if UPPER(v_cal_app_date_order) = 'ASC'
then
close data_oppdate_menu_asc;
else
close data_oppdate_menu_desc;
end if;
end if;
end if;
v_cal_int_st_time := v_cal_start_time;
v_cal_int_time := v_cal_start_time;
var_incr :=0;
LOOP
if to_date(v_cal_int_st_time,'HH:MI AM') <= to_date(v_cal_end_time,'HH:MI AM')
then
if v_cal_int_st_time = v_cal_int_time
then
var_incr := var_incr+1;
collectn_int_flag_list.extend;
collectn_int_flag_list(collectn_int_flag_list.last) :=v_cal_int_time ;
v_cal_int_time := to_char(to_date(v_cal_int_st_time , 'HH:MI AM')+to_number(v_cal_default_interval_hr)/24 + to_number(v_cal_default_interval_mn)/1440, 'HH:MI AM');
end if;
v_cal_int_st_time := to_char(to_date(v_cal_int_st_time , 'HH:MI AM')+to_number(v_cal_std_interval_hr)/24 + to_number(v_cal_std_interval_mn)/1440, 'HH:MI AM');
Else
EXIT;
end if;
END LOOP;
if var_incr > 0
then
collectn_int_flag_list.extend;
collectn_int_flag_list(collectn_int_flag_list.last) :=v_cal_int_time ;
end if;
LOOP
if to_date(v_cal_start_time,'HH:MI AM') <= to_date(v_cal_end_time,'HH:MI AM')
then
collectn_param_list :=coll_param_list();
collectn_param_colour_list :=coll_param_colour_list();
var_row_id := var_row_id + 1;
FOR collectn_int_flag_list_indx IN collectn_int_flag_list.first .. collectn_int_flag_list.last
LOOP
If collectn_int_flag_list(collectn_int_flag_list_indx) = v_cal_start_time
then
var_interval_flag := 'Y';
EXIT;
else
var_interval_flag := 'N';
end If;
END LOOP;
v_cal_start_s := v_cal_start_time;
v_cal_start_time := to_char(to_date(v_cal_start_time , 'HH:MI AM')+to_number(v_cal_std_interval_hr)/24 + to_number(v_cal_std_interval_mn)/1440, 'HH:MI AM');
v_cal_start_e := to_char(to_date(v_cal_start_time , 'HH:MI AM')+to_number(v_cal_std_interval_hr)/24 + to_number(v_cal_std_interval_mn)-1/1440, 'HH:MI AM');
var_data_counter := 0;
var_chk_previous_time :=v_cal_start_s;
var_temp_incr_id := 0;
if collectn_opp_id.count > 0
then
FOR cursor_val_column_indx IN collectn_opp_id.first .. collectn_opp_id.last
LOOP
if (to_date(collectn_opp_time(cursor_val_column_indx) , 'HH:MI AM') >= to_date(v_cal_start_s , 'HH:MI AM'))
and (to_date(collectn_opp_time(cursor_val_column_indx) , 'HH:MI AM') <= to_date(v_cal_start_e , 'HH:MI AM'))
then
If var_select_table_name = 'ANH_FSC_APPT_FIXED_DATE'
then
Select nvl(count(fsc_opportunity_id),0) into var_time_wise_count
from (
Select FSC_OPPORTUNITY_ID
from dm_opportunity_details_fsc a, dm_customer_details b
where a.lead_status=v_lead_status
and a.anh_fsc_code = v_fsc_code
and a.customer_id = b.customer_id
and ((to_date(to_char(decode(a.ANH_FSC_APPT_FIXED_DATE, null, a.TELECALLER_APPT_FIXED_DATE,a.ANH_FSC_APPT_FIXED_DATE),'dd-MON-YYYY HH:MI AM' ),'dd-MON-YYYY HH:MI AM' )
between to_date( collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM')-to_number(v_cal_default_overlap_hr)/24 - to_number(v_cal_default_overlap_mn)/1440
and to_date(collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM'))
or (to_date(to_char(decode(a.ANH_FSC_APPT_FIXED_DATE, null, a.TELECALLER_APPT_FIXED_DATE,a.ANH_FSC_APPT_FIXED_DATE),'dd-MON-YYYY HH:MI AM' ),'dd-MON-YYYY HH:MI AM' )
between to_date( to_char(to_date( collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM')-to_number(v_cal_default_overlap_hr)/24 - to_number(v_cal_default_overlap_mn)/1440, 'dd-MON-YYYY HH:MI AM') , 'dd-MON-YYYY HH:MI AM')-to_number(v_cal_default_overlap_hr)/24 - to_number(v_cal_default_overlap_mn)/1440
and to_date( collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM'))
)
union all
Select FSC_OPPORTUNITY_ID
from dm_opportunity_details_fsc a, dm_customer_details b
where a.lead_status=v_lead_status
and a.anh_fsc_code is null
and a.FSC_OPPORTUNITY_ID = opp_id_current
and a.customer_id = b.customer_id
and ((to_date(to_char(decode(a.ANH_FSC_APPT_FIXED_DATE, null, a.TELECALLER_APPT_FIXED_DATE,a.ANH_FSC_APPT_FIXED_DATE),'dd-MON-YYYY HH:MI AM' ),'dd-MON-YYYY HH:MI AM' )
between to_date( collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM')-to_number(v_cal_default_overlap_hr)/24 - to_number(v_cal_default_overlap_mn)/1440
and to_date(collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM'))
or (to_date(to_char(decode(a.ANH_FSC_APPT_FIXED_DATE, null, a.TELECALLER_APPT_FIXED_DATE,a.ANH_FSC_APPT_FIXED_DATE),'dd-MON-YYYY HH:MI AM' ),'dd-MON-YYYY HH:MI AM' )
between to_date( to_char(to_date( collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM')-to_number(v_cal_default_overlap_hr)/24 - to_number(v_cal_default_overlap_mn)/1440, 'dd-MON-YYYY HH:MI AM') , 'dd-MON-YYYY HH:MI AM')-to_number(v_cal_default_overlap_hr)/24 - to_number(v_cal_default_overlap_mn)/1440
and to_date( collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM'))
)
);
end if;
If var_select_table_name = 'DATE_STAGE_3'
then
Select nvl(count(fsc_opportunity_id),0) into var_time_wise_count
from (
Select FSC_OPPORTUNITY_ID
from dm_opportunity_details_fsc a, dm_customer_details b
where a.lead_status=v_lead_status
and a.anh_fsc_code = v_fsc_code
and a.customer_id = b.customer_id
and ((to_date(to_char(decode(a.DATE_STAGE_3, null, a.DATE_STAGE_2,a.DATE_STAGE_3),'dd-MON-YYYY HH:MI AM' ),'dd-MON-YYYY HH:MI AM' )
between to_date( collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM')-to_number(v_cal_default_overlap_hr)/24 - to_number(v_cal_default_overlap_mn)/1440
and to_date(collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM'))
or (to_date(to_char(decode(a.DATE_STAGE_3, null, a.DATE_STAGE_2,a.DATE_STAGE_3),'dd-MON-YYYY HH:MI AM' ),'dd-MON-YYYY HH:MI AM' )
between to_date( to_char(to_date( collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM')-to_number(v_cal_default_overlap_hr)/24 - to_number(v_cal_default_overlap_mn)/1440, 'dd-MON-YYYY HH:MI AM') , 'dd-MON-YYYY HH:MI AM')-to_number(v_cal_default_overlap_hr)/24 - to_number(v_cal_default_overlap_mn)/1440
and to_date( collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM'))
)
union all
Select FSC_OPPORTUNITY_ID
from dm_opportunity_details_fsc a, dm_customer_details b
where a.lead_status=v_lead_status
and a.anh_fsc_code is null
and a.FSC_OPPORTUNITY_ID = opp_id_current
and a.customer_id = b.customer_id
and ((to_date(to_char(decode(a.DATE_STAGE_3, null, a.DATE_STAGE_2,a.DATE_STAGE_3),'dd-MON-YYYY HH:MI AM' ),'dd-MON-YYYY HH:MI AM' )
between to_date( collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM')-to_number(v_cal_default_overlap_hr)/24 - to_number(v_cal_default_overlap_mn)/1440
and to_date(collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM'))
or (to_date(to_char(decode(a.DATE_STAGE_3, null, a.DATE_STAGE_2,a.DATE_STAGE_3),'dd-MON-YYYY HH:MI AM' ),'dd-MON-YYYY HH:MI AM' )
between to_date( to_char(to_date( collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM')-to_number(v_cal_default_overlap_hr)/24 - to_number(v_cal_default_overlap_mn)/1440, 'dd-MON-YYYY HH:MI AM') , 'dd-MON-YYYY HH:MI AM')-to_number(v_cal_default_overlap_hr)/24 - to_number(v_cal_default_overlap_mn)/1440
and to_date( collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM'))
)
);
end if;
If var_select_table_name = 'OPPORTUNITY_DATE'
then
Select nvl(count(fsc_opportunity_id),0) into var_time_wise_count
from (
Select FSC_OPPORTUNITY_ID
from dm_opportunity_details_fsc a, dm_customer_details b
where a.lead_status=v_lead_status
and a.anh_fsc_code = v_fsc_code
and a.customer_id = b.customer_id
and ((to_date(to_char(OPPORTUNITY_DATE,'dd-MON-YYYY HH:MI AM' ),'dd-MON-YYYY HH:MI AM' )
between to_date( collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM')-to_number(v_cal_default_overlap_hr)/24 - to_number(v_cal_default_overlap_mn)/1440
and to_date(collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM'))
or (to_date(to_char(OPPORTUNITY_DATE,'dd-MON-YYYY HH:MI AM' ),'dd-MON-YYYY HH:MI AM' )
between to_date( to_char(to_date( collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM')-to_number(v_cal_default_overlap_hr)/24 - to_number(v_cal_default_overlap_mn)/1440, 'dd-MON-YYYY HH:MI AM') , 'dd-MON-YYYY HH:MI AM')-to_number(v_cal_default_overlap_hr)/24 - to_number(v_cal_default_overlap_mn)/1440
and to_date( collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM'))
)
union all
Select FSC_OPPORTUNITY_ID
from dm_opportunity_details_fsc a, dm_customer_details b
where a.lead_status=v_lead_status
and a.anh_fsc_code is null
and a.FSC_OPPORTUNITY_ID = opp_id_current
and a.customer_id = b.customer_id
and ((to_date(to_char(OPPORTUNITY_DATE,'dd-MON-YYYY HH:MI AM' ),'dd-MON-YYYY HH:MI AM' )
between to_date( collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM')-to_number(v_cal_default_overlap_hr)/24 - to_number(v_cal_default_overlap_mn)/1440
and to_date(collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM'))
or (to_date(to_char(OPPORTUNITY_DATE,'dd-MON-YYYY HH:MI AM' ),'dd-MON-YYYY HH:MI AM' )
between to_date( to_char(to_date( collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM')-to_number(v_cal_default_overlap_hr)/24 - to_number(v_cal_default_overlap_mn)/1440, 'dd-MON-YYYY HH:MI AM') , 'dd-MON-YYYY HH:MI AM')-to_number(v_cal_default_overlap_hr)/24 - to_number(v_cal_default_overlap_mn)/1440
and to_date( collectn_opp_date(cursor_val_column_indx), 'dd-MON-YYYY HH:MI AM'))
)
);
end if;
var_cust_data_count := var_cust_data_count+1;
if var_time_wise_count > 1
then
var_temp_incr_id := var_temp_incr_id+1;
var_data_globle_counter := var_data_globle_counter+1;
var_temp := var_last_row_id + var_temp_row_id;
if var_data_globle_counter > var_data_counter AND var_row_id = var_temp+1
then
var_last_row_id := var_row_id;
var_data_counter := 0;
for blank_indx IN 1 .. var_data_globle_counter
loop
var_data_counter := var_data_counter + 1;
collectn_param_list.extend;
collectn_param_list(blank_indx) :='' ;
collectn_param_colour_list.extend;
collectn_param_colour_list(blank_indx) :='' ;
end loop;
end if;
var_data_counter := var_data_counter + 1;
collectn_param_list.extend;
collectn_param_list(var_data_counter) :=collectn_cust_name(cursor_val_column_indx)||' '||collectn_opp_time(cursor_val_column_indx) ;
collectn_param_colour_list.extend;
IF collectn_opp_id(cursor_val_column_indx) = opp_id_current
then
collectn_param_colour_list(var_data_counter) :=v_default_colour ;
else
collectn_param_colour_list(var_data_counter) :=v_column_colour ;
end if;
var_temp_row_id := 0;
else
var_chk_previous_time :=v_cal_start_s;
var_data_globle_counter := 0;
var_data_counter := 1;
collectn_param_list.extend;
collectn_param_list(var_data_counter) :=collectn_cust_name(cursor_val_column_indx)||' '||collectn_opp_time(cursor_val_column_indx) ;
collectn_param_colour_list.extend;
IF collectn_opp_id(cursor_val_column_indx) = opp_id_current
then
collectn_param_colour_list(var_data_counter) :=v_default_colour ;
else
collectn_param_colour_list(var_data_counter) :=v_column_colour ;
end if;
end if;
end if;
END LOOP;
end if;
if var_temp_incr_id > 0
then
var_last_row_id := var_row_id;
else
var_temp_row_id := var_temp_row_id + 1;
end if;
var_param_string := '';
var_param_colur_string := '';
FOR var_tb_column_count_indx IN 1 .. var_tb_column_count
LOOP
if var_tb_column_count_indx <= var_data_counter
then
var_param_string := var_param_string ||','''||collectn_param_list(var_tb_column_count_indx)||'''' ;
var_param_colur_string := var_param_colur_string||','''||collectn_param_colour_list(var_tb_column_count_indx)||'''' ;
Else
var_param_string := var_param_string||',''''';
var_param_colur_string := var_param_colur_string||',''''';
end if;
END LOOP;
var_param_final_string := substr(var_param_string,2);
var_param_final_colur_string := substr(var_param_colur_string,2);
EXECUTE IMMEDIATE 'Insert into DM_CALENDAR_DATA_TABLE (SEQ_ID, FSC_CODE, CREATED_DATE, ROW_ID, INTERVAL_TIME, INTERVAL_FLAG, START_TIME, END_TIME,COLUMN_1_DATA, COLUMN_2_DATA, COLUMN_3_DATA, COLUMN_4_DATA, COLUMN_5_DATA, COLUMN_6_DATA, COLUMN_7_DATA, COLUMN_8_DATA, COLUMN_9_DATA, COLUMN_10_DATA, COLUMN_11_DATA, COLUMN_12_DATA, COLUMN_13_DATA, COLUMN_14_DATA, COLUMN_15_DATA, COLUMN_16_DATA, COLUMN_17_DATA, COLUMN_18_DATA, COLUMN_19_DATA, COLUMN_20_DATA, COLUMN_21_DATA, COLUMN_22_DATA, COLUMN_23_DATA, COLUMN_24_DATA, COLUMN_25_DATA, COLUMN_26_DATA, COLUMN_27_DATA, COLUMN_28_DATA, COLUMN_29_DATA, COLUMN_30_DATA, COLUMN_31_DATA, COLUMN_32_DATA, COLUMN_33_DATA, COLUMN_34_DATA, COLUMN_35_DATA, COLUMN_36_DATA, COLUMN_37_DATA, COLUMN_38_DATA, COLUMN_39_DATA, COLUMN_40_DATA, COLUMN_1_COLOR, COLUMN_2_COLOR, COLUMN_3_COLOR, COLUMN_4_COLOR, COLUMN_5_COLOR, COLUMN_6_COLOR, COLUMN_7_COLOR, COLUMN_8_COLOR, COLUMN_9_COLOR, COLUMN_10_COLOR, COLUMN_11_COLOR, COLUMN_12_COLOR, COLUMN_13_COLOR, COLUMN_14_COLOR, COLUMN_15_COLOR, COLUMN_16_COLOR, COLUMN_17_COLOR, COLUMN_18_COLOR, COLUMN_19_COLOR, COLUMN_20_COLOR, COLUMN_21_COLOR, COLUMN_22_COLOR, COLUMN_23_COLOR, COLUMN_24_COLOR, COLUMN_25_COLOR, COLUMN_26_COLOR, COLUMN_27_COLOR, COLUMN_28_COLOR, COLUMN_29_COLOR, COLUMN_30_COLOR, COLUMN_31_COLOR, COLUMN_32_COLOR, COLUMN_33_COLOR, COLUMN_34_COLOR, COLUMN_35_COLOR, COLUMN_36_COLOR, COLUMN_37_COLOR, COLUMN_38_COLOR, COLUMN_39_COLOR, COLUMN_40_COLOR)values('''||p_seq_id||''','''|| v_fsc_code||''','''||SYSDATE||''','''||var_row_id||''','''|| v_cal_start_s||''','''|| var_interval_flag||''', '''||v_cal_start_s||''', '''||v_cal_start_e||''', '||var_param_final_string||','||var_param_final_colur_string||')';
-- Insert statement
DM_CALENDAR_COLOUR_UPDATE(v_max_overlap_limit,v_cal_default_overlap_hr, v_cal_default_overlap_mn, v_column_colour, p_seq_id, v_fsc_code);
Else
EXIT;
end if;
END LOOP;
COMMIT;
END IF:
OPEN cur_calendarbody_out FOR
SELECT ROW_ID, INTERVAL_TIME, INTERVAL_FLAG, START_TIME, END_TIME,COLUMN_1_DATA, COLUMN_2_DATA,
COLUMN_3_DATA, COLUMN_4_DATA, COLUMN_5_DATA, COLUMN_6_DATA, COLUMN_7_DATA, COLUMN_8_DATA,
COLUMN_9_DATA, COLUMN_10_DATA, COLUMN_11_DATA, COLUMN_12_DATA, COLUMN_13_DATA, COLUMN_14_DATA,
COLUMN_15_DATA, COLUMN_16_DATA, COLUMN_17_DATA, COLUMN_18_DATA, COLUMN_19_DATA, COLUMN_20_DATA,
COLUMN_21_DATA, COLUMN_22_DATA, COLUMN_23_DATA, COLUMN_24_DATA, COLUMN_25_DATA, COLUMN_26_DATA,
COLUMN_27_DATA, COLUMN_28_DATA, COLUMN_29_DATA, COLUMN_30_DATA, COLUMN_31_DATA, COLUMN_32_DATA,
COLUMN_33_DATA, COLUMN_34_DATA, COLUMN_35_DATA, COLUMN_36_DATA, COLUMN_37_DATA, COLUMN_38_DATA,
COLUMN_39_DATA, COLUMN_40_DATA, COLUMN_1_COLOR, COLUMN_2_COLOR, COLUMN_3_COLOR, COLUMN_4_COLOR,
COLUMN_5_COLOR, COLUMN_6_COLOR, COLUMN_7_COLOR, COLUMN_8_COLOR, COLUMN_9_COLOR, COLUMN_10_COLOR,
COLUMN_11_COLOR, COLUMN_12_COLOR, COLUMN_13_COLOR, COLUMN_14_COLOR, COLUMN_15_COLOR,
COLUMN_16_COLOR, COLUMN_17_COLOR, COLUMN_18_COLOR, COLUMN_19_COLOR, COLUMN_20_COLOR,
COLUMN_21_COLOR, COLUMN_22_COLOR, COLUMN_23_COLOR, COLUMN_24_COLOR, COLUMN_25_COLOR,
COLUMN_26_COLOR, COLUMN_27_COLOR, COLUMN_28_COLOR, COLUMN_29_COLOR, COLUMN_30_COLOR,
COLUMN_31_COLOR, COLUMN_32_COLOR, COLUMN_33_COLOR, COLUMN_34_COLOR, COLUMN_35_COLOR,
COLUMN_36_COLOR, COLUMN_37_COLOR, COLUMN_38_COLOR, COLUMN_39_COLOR, COLUMN_40_COLOR
FROM DM_CALENDAR_DATA_TABLE
WHERE seq_id = p_seq_id
AND fsc_code = v_fsc_code
ORDER BY row_id;
--FROM DM_CAL_TEST;
max_overlap_limit := TO_CHAR(v_max_overlap_limit);
END;
/
CREATE OR REPLACE PROCEDURE DM_CALENDAR_COLOUR_UPDATE(cnt_overlap in number,v_cal_default_overlap_hr in varchar2,
p_cal_default_overlap_mn in varchar2,v_column_colour in varchar2,p_seq_id in number, p_fsc_code in varchar2)
IS
TYPE cur_ref IS REF CURSOR;
cur_ref_type cur_ref;
cur_rec_dm_tmp DM_CALENDAR_DATA_TABLE%ROWTYPE;
--cur_rec_dm_tmp DM_CAL_TEST%ROWTYPE;
cnt_color number := 1;
v_query varchar2(500);
v_rowid number := 1;
v_start_min varchar2(10);
v_end_min varchar2(10);
v_col_color varchar2(25);
v_col_data varchar2(25);
v_update_color number;
val varchar2(500);
v_rowid_up varchar2(500);
v_rowid_down varchar2(500);
v_cal_default_overlap_mn varchar2(100);
v_flag number;
diff_min varchar2(10);
v_existing_colour varchar2(100);
v_existing_colour_query varchar2(5000);
begin
v_cal_default_overlap_mn := p_cal_default_overlap_mn;
v_cal_default_overlap_mn := v_cal_default_overlap_mn + (v_cal_default_overlap_hr*60);
v_cal_default_overlap_mn := (v_cal_default_overlap_mn/30);
v_query := 'SELECT * FROM DM_CALENDAR_DATA_TABLE '
|| 'WHERE seq_id = '||p_seq_id
||' AND fsc_code = '''||p_fsc_code
||''' order by row_id'; -- Changed by Soni to add seq_id and fsc_code on 8 June'09
OPEN cur_ref_type FOR v_query;
LOOP
BEGIN
FETCH cur_ref_type
INTO cur_rec_dm_tmp;
EXIT WHEN cur_ref_type%NOTFOUND;
for cnt_color in 1..cnt_overlap
loop
v_start_min := substr(trim(cur_rec_dm_tmp.start_time), instr(trim(cur_rec_dm_tmp.start_time),':')+1,2);
v_end_min := substr(trim(cur_rec_dm_tmp.end_time), instr(trim(cur_rec_dm_tmp.end_time),':')+1,2);
v_col_color := 'column_'||cnt_color||'_color';
v_col_data := 'column_'||cnt_color||'_data';
v_query := 'Select '||v_col_data||' from DM_CALENDAR_DATA_TABLE '
|| 'WHERE seq_id = '||p_seq_id
||' AND fsc_code = '''||p_fsc_code
||''' AND row_id = '||v_rowid
||' order by row_id'; -- Changed by Soni to add seq_id and fsc_code on 8 June'09
execute immediate v_query into val;
v_existing_colour := '';
v_existing_colour_query := '';
v_existing_colour_query := 'Select '||v_col_color||' from DM_CALENDAR_DATA_TABLE '
|| 'WHERE seq_id = '||p_seq_id
||' AND fsc_code = '''||p_fsc_code
||''' AND row_id = '||v_rowid ; -- Changed by Soni to add seq_id and fsc_code on 8 June'09
execute immediate v_existing_colour_query into v_existing_colour;
IF val IS NOT NULL THEN
v_query := 'Select substr('''||val||''',instr('''||val||''','':'')+1,2) from DM_CALENDAR_DATA_TABLE '
|| 'WHERE seq_id = '||p_seq_id
||' AND fsc_code = '''||p_fsc_code
||''' AND row_id = '||v_rowid ; -- Changed by Soni to add seq_id and fsc_code on 8 June'09
execute immediate v_query into diff_min;
v_query := 'Select decode(SIGN('||diff_min||'-('||v_start_min||'+15)),-1,10,20) from dual';
execute immediate v_query into v_flag;
getrowid(v_flag,v_rowid,v_cal_default_overlap_mn,v_rowid_up,v_rowid_down);
v_rowid_up := substr(v_rowid_up,1,length(v_rowid_up)-1);
v_query := ' Update DM_CALENDAR_DATA_TABLE set '||v_col_color||' = '''||v_existing_colour||'''' ||
' where row_id IN ('||v_rowid||','||v_rowid_up||')'
||' AND seq_id = '''||p_seq_id
||''' AND fsc_code = '||p_fsc_code; -- Changed by Soni to add seq_id and fsc_code on 8 June'09
execute immediate v_query;
END IF;
END LOOP;
v_rowid := v_rowid + 1;
end;
end loop;
commit;
end;
/
CREATE OR REPLACE PROCEDURE getrowid(p_flag in number,v_rowid in number,p_cal_default_overlap_mn in number,p_rowid_up out varchar2,
p_rowid_down out varchar2)
IS
v_cal_default_overlap_mn number := p_cal_default_overlap_mn;
row_ins varchar2(100);
v_rowid_up varchar2(100);
v_rowid_down varchar2(100);
v_rowid_val varchar2(30);
v_flag number:=p_flag;
BEGIN
for num_rowid in -v_cal_default_overlap_mn..v_cal_default_overlap_mn
loop
v_rowid_val := num_rowid;
If v_flag = 10 then
v_rowid_up := to_number(v_rowid+v_rowid_val)||',';
else
if v_rowid_val != (v_cal_default_overlap_mn) then
SELECT decode(sign(v_rowid_val-0),-1,v_rowid_val,'+'||v_rowid_val) into row_ins from dual;
if p_flag = 10 then
v_rowid_up := v_rowid_up|| to_number(v_rowid+row_ins)||',';
elsif p_flag = 20 then
v_rowid_up := v_rowid_up|| to_number(v_rowid+row_ins+1)||',';
end if;
else
SELECT decode(sign(v_rowid_val-0),-1,v_rowid_val,'+'||v_rowid_val) into row_ins from dual;
end if;
end if;
v_rowid_val := v_rowid_val +1;
v_flag := 1;
end loop;
p_rowid_up := v_rowid_up;
p_rowid_down := v_rowid_down;
END;
/
Thanks for looking into it,
Soni
[Updated on: Mon, 15 June 2009 01:00] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Database stops responding when run on production server [message #408973 is a reply to message #408178] |
Thu, 18 June 2009 09:18  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
It is very unlikely that anyone is even going to start looking at that much code.
1) It's huge
2) It references tables whose structures we don't know, which have indexes we don't know, constraints we don't know, and contain data we don't know
3) We don't know what the code does, or what it is meant to do.
4) We don't even know what the problem is - all you've told us is " it started consuming memory very fast and database was restarted because of it."
-- What memory? How did you observe this problem?
-- did you shutdown the database, or did it crash
-- did you try killing the session that the procedure was running in? If so, what happened?
-- when you ran it on UAT, did it run against the same set of data, with the same statistics?
Try running the code again, and run a statspack / AWR report while it is running - see what that says are the performance problems.
|
|
|
|
Goto Forum:
Current Time: Sun Jun 23 13:44:30 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
