Solved

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

Posted on 2002-07-09
15
22,719 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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
 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

726 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