Another ORA-06502: PL/SQL: numeric or value error: character to number conversion er
I have been fighting with this (I know I shouldn't fight with code!!) Can someone see what I am not seeing?
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "SYSTEM.STATUS_DESC_SF", line 17
ORA-06512: at line 5
I just do not see the error. In the DB the idstage column is a NUMBER but I am not seeing stage_id to other than a numeric value just using it in a conditional.
CREATE OR REPLACE FUNCTION STATUS_DESC_SF
(stage_id IN NUMBER)
RETURN NUMBER
IS
stat_desc VARCHAR2(50);
BEGIN
CASE WHEN stage_id = 1 THEN
stat_desc:= 'Order Submitted';
WHEN stage_id = 2 THEN
stat_desc := 'Accepted, sent to shipping';
WHEN stage_id = 3 THEN
stat_desc := 'Backordered';
WHEN stage_id = 4 THEN
stat_desc := 'Cancelled';
ELSE stat_desc := 'Shipped';
END CASE;
RETURN stat_desc;
END;
/
DECLARE
stat_desc VARCHAR2(50);
stage_id NUMBER(1) := 4;
BEGIN
stat_desc := STATUS_DESC_SF(stage_id);
DBMS_OUTPUT.PUT_LINE(stat_desc);
END;
/