Solved

TO_CHAR function & ORA-01722: invalid number error

Posted on 2014-12-01
6
828 Views
Last Modified: 2014-12-01
Gurus,

I receive ORA-01722: invalid number error when running this query.

select TO_CHAR(PRSPRSC/100/2,'fm999.00') NOW_PRICE from prs;

What is the best way around this?
0
Comment
Question by:xbox360dp
  • 3
  • 2
6 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40474430
>> What is the best way around this?

Is PRSPRSC a number data type or a varchar2 that has numbers in it?

If a varchar2, the best way to fix it is fix the 'bad' data that is likely causing the problem.

The other choice is to write your own to_number function that can deal with non-numeric data the way you want to handle it and use that in the call.

If you create my_to_number then:
TO_CHAR(my_to_number(PRSPRSC)/100/2,'fm999.00')

You would need to decide on what to return if the data is not a number.
0
 

Author Comment

by:xbox360dp
ID: 40474470
PRSPRSC is a varchar2 that has numbers in it.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40474476
>>PRSPRSC is a varchar2 that has numbers in it.

then see the recommendations above:
1: Fix the data.
2: Write your own function to handle the 'bad' data.
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 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40474511
I agree, writing your own function would probably be best, but as a temporary measure ,you could try a case.

TO_CHAR(case when regexp_like(PRSPRSC,'^[0-9]*\.?[0-9]+$') then PRSPRSC/100/2 end,'fm999.00')

this will cause your non-numeric strings to return null

alternately, put the conversion inside the case, and return the original string when it's not valid so it's easily visible

 CASE
           WHEN REGEXP_LIKE(prsprsc, '^[0-9]*\.?[0-9]+$') THEN TO_CHAR(prsprsc / 100 / 2, 'fm999.00')
           ELSE prsprsc
       END


note, these regexps aren't guaranteed to capture all weird data entry, but will probably work for most.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40474558
As for a custom function, I would put all of the formatting into that rather than converting to number and then formatting.  That way you can encapsulate all of the rules nicely in one place.


CREATE OR REPLACE FUNCTION formatter(p_str IN VARCHAR2, p_format IN VARCHAR2 )
    RETURN VARCHAR2
IS
    v_temp NUMBER;
BEGIN
    v_temp := TO_NUMBER(p_str);

    -- If a format is given use it, otherwise use the default format for numbers
    IF p_format IS NOT NULL
    THEN
        RETURN TO_CHAR(v_temp, p_format);
    ELSE
        RETURN TO_CHAR(v_temp);
    END IF;
EXCEPTION
    WHEN OTHERS
    THEN
        RETURN p_str;  -- change this to whatever you want non-numerics to do.
END;

Open in new window



and then usage would look like this...


select formatter(PRSPRSC/100/2,'fm999.00') NOW_PRICE from prs;
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40475027
The accepted solution is a temporary fix at best and as noted, has potential flaws moving forward in someone enters something that is 'close' to a number but not exactly a number.

If they do that, you are right back where you are now.

Something is allowing 'bad' data into your tables and can lead to very bad results depending on the application.

First thing you really need to do is stop storing numbers in a varchar2 column.  Varchar2 is for strings.  number is for numbers.

Since you likely cannot do that, I would look at a job/script that at least monitors the table for 'bad' data daily/weekly and fix it as it comes in.

Better approach is what I mentioned and sdstuber posted: your own function.
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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
report returning null 21 95
Get FileTypes from a list of FileNames  in a Table field 18 49
SQL Query 34 99
How to Gracefuly recover in Racle stored procedure 1 30
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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

773 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