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 Go to next message
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 #408177 is a reply to message #408172] Mon, 15 June 2009 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Thanks for looking into it,


./fa/449/0/

Re: Database stops responding when run on production server [message #408178 is a reply to message #408177] Mon, 15 June 2009 01:39 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Embarassed
Michel please don't laugh as I am in a real bad situation. Since you have looked into the code (assumed).... do u see a big problem with the code....I am not very experienced with writting big procedures...this is the first and most complex one by me....please help me or give some hints atleast.

Regards,
Soni

[Updated on: Mon, 15 June 2009 01:58]

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 Go to previous message
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.

Previous Topic: is there any need of rebuild pk index
Next Topic: Improving Performance of Large Result Set
Goto Forum:
  


Current Time: Fri Dec 09 05:58:04 CST 2016

Total time taken to generate the page: 0.24045 seconds