DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Another ORA-06502: PL/SQL: numeric or value error: character to number conversion er

  1. #1
    Join Date
    Mar 2009
    Posts
    2

    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;
    /

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Talking Take a closer look!

    DUH! Check this out:
    Code:
    QL> CREATE OR REPLACE FUNCTION STATUS_DESC_SF
      2  (stage_id IN NUMBER)
      3  RETURN NUMBER
      4  IS 
      5  stat_desc VARCHAR2(50);
      6  BEGIN
      7  CASE WHEN stage_id = 1 THEN 
      8  stat_desc:= 'Order Submitted';
      9  WHEN stage_id = 2 THEN 
     10  stat_desc := 'Accepted, sent to shipping';
     11  WHEN stage_id = 3 THEN 
     12  stat_desc := 'Backordered';
     13  WHEN stage_id = 4 THEN
     14  stat_desc := 'Cancelled';
     15  ELSE stat_desc := 'Shipped';
     16  END CASE;
     17  RETURN stat_desc;
     18  END;
     19  /
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Mar 2009
    Posts
    2
    Yep, it smacked me in the face after I posted the thread. I don't know what the heck I was thinking.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width