Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error

abhishek.ankur_at_gmail.com wrote:
> HI everybody
>
> Plz help me out. I am stuck in a section of code.
> I have created a procedure which gets called from a trigger. The
> procedure is taking an INPUT parameter from the trigger and calculating
> some value.
>
> Here is the code for my procedure
>
>
> CREATE or REPLACE PROCEDURE generate_ISBN13
> (
> p_in_isbn10 IN a_masterisbn.Full_ISBN%TYPE)
> IS
> v_isbn13 VARCHAR2(17);
> v_t1 VARCHAR2(2);
> v_t2 VARCHAR2(15);
> v_t3 VARCHAR2(20);
> v_t4 VARCHAR2(15);
> v_t5 NUMBER(15);
> v_a NUMBER(1);
> v_b NUMBER(1);
> v_sum NUMBER(4) := 0;
> v_checkd NUMBER(1);
> v_last VARCHAR2(2);
> BEGIN
> v_t1 := substr(p_in_isbn10,12,1);
> v_t2 := RTRIM(p_in_isbn10,v_t1);
> v_t3 := '978-'||v_t2;
> v_t4 := replace(v_t3,'-','');
> v_t5 := TO_NUMBER(v_t4);
> for v_counter in 1 .. 6 Loop
> v_a := mod(v_t5,10);
> v_sum := v_sum + (v_a * 3);
> v_t4 := trunc(v_t5/10);
> v_b := mod(v_t5,10);
> v_sum := v_sum + (v_b * 1);
> v_t4 := trunc(v_t5/10);
> END LOOP;
> v_checkd := mod(v_sum,10);
> v_last :='-'||v_checkd;
> v_isbn13 := concat(v_t3,v_last);
> INSERT into a_isbn_rel values(p_in_isbn10,v_len);
> END;
>
>
> p_in_isbn10 IN a_masterisbn.Full_ISBN%TYPE ........ a_masterisbn is a
> database table which has an attribute Full_ISBN.
>
> When i run the trigger i constantly get an error : ORA-06502: PL/SQL:
> numeric or value error: character to number conversion error
>
> I am un able to figure out the problem.
>
> Moreover the substr function seems not working properly. FOr an input
> of 0-123-456789-2 it should return '-2' but its returning only '-'
>
>
> Plz help out

Your Oracle version and the actual error message would be helpful. At which line is the error being reported?

v_t4 := trunc(v_t5/10);

looks like a likely candidate: v_t4 is a VARCHAR2 and you are trying to stuff a number into it. Never rely on implicit conversion.

-- 
Puget Sound Oracle Users Group