Solved

Error -6502: ORA-06502: PL/SQL: numeric or value error:

Posted on 2002-07-09
15
22,588 Views
Last Modified: 2012-06-21
very urgent, please help.
i am getting this error and ihave tried to rmove the spaces in the data and stuff. it din work. is there ary proceudre i can use to remove all spaces or junk values like # or % and stufff..

error:
Error -6502: 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

one piece of code, where the error is coming, i guess:

IF TRUNC (
TO_NUMBER (
LTRIM (RTRIM (diag_rec.sec_diag6)))) = TRUNC (TO_NUMBER (v_icd_main))
THEN
v_icd := v_icd+ 1;
END IF;

datatypes associated with that:
v_icd_main VARCHAR2 (50)
v_icd VARCHAR2 (50)
v_icd250 VARCHAR2 (50) := 0;


it works for a small dataset with data. when i try a huge datasset somewhere its geting messed up. i am asuming its some space in some blank fields.
how do i remove spaces inside data columns.

0
Comment
Question by:ezee
15 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7140800
well
just store it in variables before you compare

like
begin
  begin
    v_1 := TRUNC (TO_NUMBER (LTRIM (RTRIM (diag_rec.sec_diag6))));
    v_2 := TRUNC (TO_NUMBER (v_icd_main));
  exception
    when others then --or other handling
      v_continue := false;
  end;
  if v_continue then
    if v_1=v_2 then
      ...
    end if;
  else
    ---maybe other handling
  end if;
....

just from head as suggestion

meikl ;-)


0
 
LVL 3

Expert Comment

by:mnicoras
ID: 7140801
Hi,

I suggest to use the function replace(diag_rec.sec_diag6,' ') instead of LTRIM(RTRIM(...

ok?

best regards,
Marius Nicoras
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7140817
maybe the translate function helps->just evaluating now
0
 
LVL 27

Accepted Solution

by:
kretzschmar earned 500 total points
ID: 7140836
well, translate could do the job like

select
translate('acbd+20.3#$%','1234567890.ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzß!"§$%&/()=?+*#-_:;',
                         '1234567890.                                                                       ') from dual

results in 20.3 and many some blanks, which could be trimed away ltrim/rtrim as you already do it above

meikl ;-)
0
 
LVL 11

Expert Comment

by:pennnn
ID: 7141069
kretzschmar,
If you use the following syntax in your translate statement, there won't be any spaces to be trimmed:
select
translate('acbd+20.3#$%','1234567890.ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzß!"§$%&/()=?+*#-_:; ',
                        '1234567890.') from dual

If a space is the only possible "bad" character, then Marius' solution using replace is better, or at least more readable...
I think the real questions are:
- If the field is supposed to contain numbers why isn't its datatype set to NUMBER?
- If the field is supposed to contain numbers why does it contain other characters?
- If it does contain other characters, why the heck are you going to convert them to numbers? How are you going to decide what number is the following string: "9# 9"?! Is it 99? At least that's what you're going to get using kretzschmar's query.

If your fields are supposed to contain only numbers, then convert them to a NUMBER datatype or at least perform a one-time update (using translate or replace or whatever) to get rid of the "bad" characters!
Hope that helps!
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7141080
well, was adhoc, pennnn with 4n ;-)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7141096
btw.
i prefer my first comment,
which just rejects unusual records,
they could be logged and an
user could then correct the entries

but it depends on the needs of the questioner

meikl ;-)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 5

Expert Comment

by:kelfink
ID: 7141381
ezee,

Looks like you're handling ICD9 or ICD10 coding.

In case you're just gettign into that stuff, remember that there are V codes, and, I think some others, where the code starts with a letter.  Taking the letter off is not valid, I think.  Changes the meaning of the code.

It's been a few years since I worked with diagnosis codes.

I certainly never performed any math operations on them.  Maybe you're rounding up, to find the general code instead of a specific one?

If you want to perform those operations, I'd take the first letter off (if it's a letter) then put it back on later.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7154371
hello?
0
 

Author Comment

by:ezee
ID: 7154552
i didn't follow any of the above. But would like to split the points to kretzschmar and pennnn. I would like to thank kelfink for his advice, as it makes sense, for my work.
how do i split the points.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7154712
ask a 0-pts question at community support
for splitting points
0
 

Author Comment

by:ezee
ID: 7157339
i din get u. but i made a 0-point question but i guess i made a mistake by giving 250 points. it doent take 0 points for that. i really don get it. sorry. please explain.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7157373
goto
http://www.experts-exchange.com/commspt/

ask there a question for splitting points like
(assign 0 points to this new q there)

please split points for my q
http://www.experts-exchange.com/oracle/Q_20321264.html
between kretzschmar and pennnn (with 4n ;-)

a moderator will then pick this question and assist you

delete your 0-point question here

hope i was clear enough

meikl ;-)
0
 

Author Comment

by:ezee
ID: 7162446
kretzschmar: I have decided to award the full points to you, as I happened to use your idea in my recent data cleaning operation and it worked like charm.
Thanks a lot.
ks
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7162663
oops,
never guessed that
thanks :-)

glad to helped you,
good luck again

meikl ;-)

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now