Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2002-07-09
15
Medium Priority
?
22,898 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Accepted Solution

by:
kretzschmar earned 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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…

610 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