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

From: <abhishek.ankur_at_gmail.com>
Date: 4 Sep 2006 02:53:18 -0700
Message-ID: <1157363598.534424.3390@e3g2000cwe.googlegroups.com>

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 Received on Mon Sep 04 2006 - 04:53:18 CDT