This discussion is archived
4 Replies Latest reply: Feb 6, 2013 12:34 AM by 936093 RSS

Oracle APEX: Getting numeric or value error

936093 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points