Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL: numeric or value error: character to number conversion error
PL/SQL: numeric or value error: character to number conversion error [message #224660] Thu, 15 March 2007 00:55 Go to next message
mallikj2
Messages: 7
Registered: March 2007
Junior Member
I meet with this error when i tried to CAST a VARCHAR INTO NUMBER

.....
IF ( CAST( l_tab(i).name AS NUMBER)) = 47 THEN
DBMS_OUTPUT.PUT_LINE('xxxxxxxxx');
END IF;
........

Inside the l_tab the name filed contains no value at position 1, but 47 at position 2.
WHEN I changed the condition as CAST( l_tab(2).name AS NUMBER) then it working ( i made the change just to check whether the cast is working or not ).

i even tried to check before with it as
IF l_tab(i).name IS NULL THEN
l_dummy := 0;
ELSE
l_dummy := CAST( l_tab(i).name AS NUMBER);
END IF;

Even Then also am getting the PL/SQL: numeric or value error: character to number conversion error, WHY? and how can i overcome with this
Re: PL/SQL: numeric or value error: character to number conversion error [message #224677 is a reply to message #224660] Thu, 15 March 2007 02:13 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
Inside the l_tab the name filed contains no value at position 1
What exactly you have? a NULL? a space?

By
Vamsi
Re: PL/SQL: numeric or value error: character to number conversion error [message #224705 is a reply to message #224677] Thu, 15 March 2007 03:47 Go to previous messageGo to next message
mallikj2
Messages: 7
Registered: March 2007
Junior Member
i have a space in it
Re: PL/SQL: numeric or value error: character to number conversion error [message #224709 is a reply to message #224705] Thu, 15 March 2007 03:55 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
That's it.
Space is not a number. Right?
User trim or replace on top of l_tab(i).name

But how come you are storing numbers in name column?

By
Vamsi
Previous Topic: How to check whether Varray is Null or Not.--V.Urgent
Next Topic: Some very strange problem
Goto Forum:
  


Current Time: Thu Dec 08 18:43:27 CST 2016

Total time taken to generate the page: 0.09501 seconds