Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06502: PL/SQL: numeric or value error: character to number conversion error [message #244643] Wed, 13 June 2007 10:53 Go to next message
toastmax
Messages: 16
Registered: November 2006
Junior Member
I've got a function that looks at a middle name, looks and sees if it looks like 'Smith Jones' and returns 'Smith'.

If runs ok when I do :

select give_name_1('Smith Jones') from dual;


But when I use it in a query, it runs for the first 75 rows and comes up with :

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

This is the code :

FUNCTION GIVEN_NAME_1(IN_MIDNAME IN VARCHAR2) RETURN VARCHAR2
IS
I	NUMBER(3);
X	VARCHAR2(30) DEFAULT '' ;
BEGIN

IF IN_MIDNAME = '' THEN
	X:= '';
ELSE
	I:=INSTR(IN_MIDNAME, CHR(32));
	IF I > 0 THEN
		X:= SUBSTR(1, IN_MIDNAME, I);
	ELSE
		X:= IN_MIDNAME;
	END IF;
END IF;
RETURN X;
END GIVEN_NAME_1;


The database is Oracle 10g 10.2.0.3.0

Any suggestions?
Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error [message #244645 is a reply to message #244643] Wed, 13 June 2007 11:05 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Have you looked at the definition of the substr function?

SUBSTR(char,m[,n]) 
 A substring of char, beginning at character m, n characters 
 long (if n is omitted, to end of char) 
Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error [message #244646 is a reply to message #244643] Wed, 13 June 2007 11:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
IF IN_MIDNAME = '' THEN

This will never happen. Nothing is equal to ''. Change to:
IF IN_MIDNAME IS NULL THEN

Quote:
X:= SUBSTR(1, IN_MIDNAME, I);
ELSE
X:= IN_MIDNAME;

As X is declared as VARCHAR2(30), maybe IN_MIDNAME or SUBSTR(1, IN_MIDNAME, I); is greater than 30 (bytes?).

Regards
Michel
Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error [message #244652 is a reply to message #244643] Wed, 13 June 2007 11:17 Go to previous messageGo to next message
techno
Messages: 44
Registered: October 2003
Member
I think the first name length is more than 30 characters
increase the length of the variable X


Regards

Techno
Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error [message #244821 is a reply to message #244652] Thu, 14 June 2007 03:24 Go to previous message
toastmax
Messages: 16
Registered: November 2006
Junior Member
Found the error, it was in the SUBSTR usage

Function now looks like :

 
FUNCTION GIVEN_NAME_1(IN_MIDNAME IN VARCHAR2) RETURN VARCHAR2
IS
I	NUMBER(3);
X	VARCHAR2(255) DEFAULT '' ;
BEGIN

IF IN_MIDNAME IS NULL THEN
	X:= IN_MIDNAME;
ELSE
	I:=INSTR(IN_MIDNAME, CHR(32));
	IF I > 0 THEN
		X:= SUBSTR(IN_MIDNAME,1,I);
	ELSE
		X:= TRIM(IN_MIDNAME);
	END IF;
END IF;

RETURN X;
END GIVEN_NAME_1;


Thanks for all your help
Previous Topic: select single record not using where clause
Next Topic: How can i Raise message/exception on commit
Goto Forum:
  


Current Time: Sun Dec 11 04:10:42 CST 2016

Total time taken to generate the page: 0.15343 seconds