Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502: PL/SQL: numeric or value error (merged)
ORA-06502: PL/SQL: numeric or value error (merged) [message #119289] Wed, 11 May 2005 11:19 Go to next message
milko_gonzalez
Messages: 2
Registered: May 2005
Location: Venezuela
Junior Member
Recently we made the migration of Oracle 7.3 to Oracle 9i.
However, we have problems using codes like the following example:

DECLARE
    var NUMBER;
BEGIN
    var := ' ';
END;


On Oracle 7.3 it compiles and executes without problems because the blank space is interpreted as zero (0).

On Oracle 9i it complies but appears an execution error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error.

We already know the origin of this error, but we want to find an efficient and quick solution since we have a lot of procedures that could be presenting the same issue.

My question:
1) Oracle 9i have a configuration where you could specify that blank spaces would be interpreted as zero values (0) (same as Oracle 7.3)?
2) Is there any way that I would receive an error with this at moment of compiling my procedure?

Thanks a lot.
Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error [message #119296 is a reply to message #119289] Wed, 11 May 2005 11:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
i dont have an oracle 7.3 around and i cannot test what i am saying.
anything between two quotes ' is considered as character.
so a blank space ' ' is a character even in 7.3.
And i believe, it would have never worked in 7.3 also.

afiedt.buf" 6 lines, 54 characters

1 DECLARE
2 var NUMBER;
3 BEGIN
4 var := null;
5* END;
scott@9i > /

PL/SQL procedure successfully completed.

scott@9i > DECLARE
2 var NUMBER;
3 bEgin
4 var :=' ';
5 end;
6 /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4


Anyhow to answer your question.
option 1.) I am not aware of any such facility.
But doing so will be dangerous.
You need to fix the code as part of your 'upgrade'.
Fixing the database features is NOT considered as an upgrade.

option 2.) I dont understand
Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error [message #119297 is a reply to message #119296] Wed, 11 May 2005 11:59 Go to previous messageGo to next message
milko_gonzalez
Messages: 2
Registered: May 2005
Location: Venezuela
Junior Member
On Oracle 7.3 work.
2) There is any way that Oracle show me an error in compilation time?
Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error [message #119299 is a reply to message #119297] Wed, 11 May 2005 12:09 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator

You can see the session.
I compiled the anonymous pl/sql block.
It gave the error.

But when You convert that to a stored procedure.
It will compile good.
But errors only when it executes.
it is how the stored procedures works.
Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error [message #119300 is a reply to message #119299] Wed, 11 May 2005 12:11 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>On Oracle 7.3 work.

could you post a sample session from 7.3?

1.
select * from v$version;

2.
DECLARE
var NUMBER;
BEGIN
var := ' ';
END;
Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error [message #119302 is a reply to message #119289] Wed, 11 May 2005 12:30 Go to previous messageGo to next message
Ivan
Messages: 180
Registered: June 2000
Senior Member
The answer to your second question is obvious: You won't get an error message, because the value (space in your case) is validated (its length) at run time (this is a usual thing in any programming language).

For your first question, you can try to set Oracle's DB compatibility to a lower version, but in this case you'll loose the new features of a "more advanced" version.

If the "space assignment" problem has place only in your DB code, you can easily find all the places you have such assignments, with a query similar to this:

SELECT * FROM user_source WHERE text LIKE '%:=%'' ''%';


You'll also have to check your triggers' code.

Good luck.
Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error [message #119304 is a reply to message #119289] Wed, 11 May 2005 12:49 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Also make sure it is two quotes with a space between, and not two quotes without (which is null). A space will not go into a number, but a null will (unless of course it is not null).

MYDBA@ORCL > declare
  2  var number;
  3  begin
  4  var := '';
  5  end;
  6  /

PL/SQL procedure successfully completed.

MYDBA@ORCL > declare
  2  var number;
  3  begin
  4  var := ' ';
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4

Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error [message #119305 is a reply to message #119289] Wed, 11 May 2005 12:50 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
And please don't cross post the same question in multiple places.
Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error [message #119306 is a reply to message #119304] Wed, 11 May 2005 12:54 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
martin nailed it.
That MUST be the case.
Last time i logged into oracle 7.x is by 1996 !.
Never came across any such concept that convert blanks spaces to zeros.
Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error [message #119307 is a reply to message #119305] Wed, 11 May 2005 13:13 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
smartin wrote on Wed, 11 May 2005 19:50

And please don't cross post the same question in multiple places.



Topics merged.

@original poster: we heard you the first time...

MHE
Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error [message #199383 is a reply to message #119307] Mon, 23 October 2006 20:59 Go to previous messageGo to next message
artmast
Messages: 11
Registered: October 2006
Junior Member
For some reason this is one error I seem to get all the time. And I can never remember the error. I don't understand why there aren't separate errors for buffer length, character type ets.

[Updated on: Thu, 30 November 2006 10:03] by Moderator

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error [message #199421 is a reply to message #199383] Tue, 24 October 2006 03:23 Go to previous message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hm? Did you, actually, say that although you see ORA-06502 very often, you can't remember what it means? Do you, perhaps, suffer from memory disorder?
Previous Topic: Outer join problem
Next Topic: ORA-01403: no data found
Goto Forum:
  


Current Time: Tue Dec 06 14:18:16 CST 2016

Total time taken to generate the page: 0.05871 seconds