4 Replies Latest reply: Feb 6, 2013 2:34 AM by 936093 RSS

    Oracle APEX: Getting numeric or value error

    936093
      Hello All,

      I am getting numeric or value error while executing below code. Please help on this. Getting error while adding color in <td>. I have put comment on error place.
      DECLARE
      start_roster date;
      end_roster date;
      start_date date;
      end_date date;
      set_count varchar2(200);
      set_count1 varchar2(200);
      count_number number;
      V_ALL_DATA CLOB;
      v_bg_color varchar2(100):='black';
      PROD number;
      PROJ number;
      PROD_M number;
      ts1 number;
      ts2 number;
      ts3 number;
      set_val varchar2(5000);
      shift_detail varchar2(100);
      V_CO varchar2(50);
      BEGIN
      V_CO:='WHITE';
      V_ALL_DATA:='<table border=0 cellspacing=5 width="100%" align="center">
      <tr>
      <td align="center" colspan="25"> Roster Report </td>
      </tr>';
      select min(distinct(Week)) into start_roster from DSR_ROASTER_REPORT;
      select max(distinct(Week)+1) into end_roster from DSR_ROASTER_REPORT;
      while to_date(end_roster,'mm/dd/yyyy') >= to_date(start_roster,'mm/dd/yyyy')
      loop
      V_ALL_DATA:=V_ALL_DATA ||'<td>'||substr(start_roster,4,2)||'</td>';
      start_roster:=start_roster + 1;
      end loop;
      
      V_ALL_DATA:= V_ALL_DATA ||'<!--Main Data--></tr>';
      select min(distinct(Week)) into start_roster from DSR_ROASTER_REPORT;
      select max(distinct(Week)+1) into end_roster from DSR_ROASTER_REPORT;
      --while to_date(end_roster,'mm/dd/yyyy') >= to_date(start_roster,'mm/dd/yyyy') loop
      for c1 in (select wwid,name,support from DSR_ROSTER_FINAL where to_date(end_roster,'mm/dd/yyyy') >= to_date(start_roster,'mm/dd/yyyy'))
      loop
      select min(distinct(Week)) into start_roster from DSR_ROASTER_REPORT;
      select max(distinct(Week)+1) into end_roster from DSR_ROASTER_REPORT;
      V_ALL_DATA := V_ALL_DATA || '<tr><td bgcolor="#A9BCF5">'||c1.name||'</td><td>'||c1.support||'</td>';
      
      while to_date(end_roster,'mm/dd/yyyy') >= to_date(start_roster,'mm/dd/yyyy')
      loop
      
      set_val:='select M'||substr(start_roster,4,2)||' from DSR_ROSTER_FINAL where wwid='''||c1.wwid||'''';
      EXECUTE IMMEDIATE set_val into shift_detail;
      if shift_detail='CO' then
      V_CO:='RED';
      else
      V_CO:='WHITE';
      end if;
      
      V_ALL_DATA := V_ALL_DATA || '<td bgcolor="green">'||shift_detail||'</td>'; <!-- Getting error while adding color-->
      
      start_roster:=start_roster + 1;
      end loop;
      V_ALL_DATA := V_ALL_DATA || '</tr>';
      end loop;
      --
      --end loop;
      
      V_ALL_DATA := V_ALL_DATA || '</table>';
      
      HTP.P(V_ALL_DATA);
      END;
      APEX Version:4.1.1
      Full DB/version/edition/host OS : 11g R2 / Linux RHEL 5
      Web server architecture (EPG, OHS or APEX listener/host OS) : EPG
      Browser(s) and version(s) used: Firefox & IE9
      Theme: Simple Grey - 9