Online Tech Support

This tutorial is based on examples to be easier to follow. The Oracle To_Number data conversion allows doing explicit data conversions and this means in other words that it does force your value to become a number type. You will need this function when your numeric values are stored into a text column as for example VARCHAR2. When you are using two different data type in a join condition or in Oracle Union operator there is a change they will fail and your SQL query will finish with an error. It is safer to convert all values in explicit way to same data type than to let Oracle do implicit data conversion and hope it does it in correct way. The syntax of Oracle TO_NUMBER is:

TO_NUMBER (<your_value>)

You are able to convert to number only the following other data types: CHAR, VARCHAR2, NCHAR, NVARCHAR2, BINARY_FLOAT and BINARY_DOUBLE.

The following example does convert the Rownum pseudocolumn to a number. The function returns always number values there shouldn’t be any difficulties to convert it to a number.

 SELECT TO_NUMBER (rownum)
   FROM dual;

The output above shows a number 1 since we do have only one row. In the next Online Tech Support example we are going to use the Dummy column from the Oracle Dual table. The column value is char “X” then that should quite difficult to make a number. Take a look at the example below.

 SELECT TO_NUMBER (rownum)
   FROM dual;

As we did mention above the conversion to a number will be quite difficult or in other words impossible. The function couldn’t turn the “X” to a number and the SQL query raised error ORA-01722: invalid number. You can only convert numbers saved into a text or other data types to number types.

The following Select statement is showing the data we are going to use in our third example below. The query returns 10 lines and every 4th line returns the “X” char from the Dummy column.

 SELECT DECODE (MOD (rownum,4),0,dummy,rownum) AS ID
   FROM dual
    CONNECT BY rownum < 11;

This example below should show us what happens with the SQL query that has mixed value types and we are going convert them all to numbers. To see the see the data please have a look at the last example above.

 SELECT TO_NUMBER (qry.ID)
   FROM
    (SELECT DECODE (MOD (rownum,4),0,dummy,rownum) AS ID
       FROM dual
        CONNECT BY rownum < 11
    ) qry;

The Oracle Select returned error ORA-01722: invalid number again. This is not a common practice, but sometimes the data does come from other sources in a mixed way and we would need to take out only number values when they are available. To do so our computer technician has written a simple function.

 CREATE FUNCTION get_only_numbers (P_TEXT VARCHAR2)
  RETURN NUMBER
 IS
 BEGIN
   RETURN TO_NUMBER(P_TEXT);

 EXCEPTION when VALUE_ERROR
           then RETURN null;
 END get_only_numbers;

The customised Oracle function has input text variable named P_TEXT and it returns only numbers. Inside the function we will try to do conversion with function TO_NUMBER and when it fails the function raises the build-in VALUE_ERROR error. The last part of function the VALUE_ERROR error will be caught by the EXCEPTION when clause and it returns NULL (empty) value. This function allows us to filter out only number values and to avoid the ORA-01722: invalid number error. Now has left to see how the customised function with our Select statement.

 SELECT get_only_numbers(qry.ID) only_numbers
   FROM
    (SELECT DECODE (MOD (rownum,4),0,dummy,rownum) AS ID
       FROM dual
        CONNECT BY rownum < 11
    ) qry;

The SQL query returned only numbers and works without raising errors. As we did already mention above this function is not very common practice but sometimes we do have some extreme needs and this Oracle function will help you out in this case.

See Also:
Home