Geeks With Blogs
Malisa Ncube - .NET Delights .NET Development ideas and things

I have found a very interesting scenario which reflects problems you may have when you are working with Oracle. I’m using Oracle 9i and I have been trying to extract numeric data out of a list of data items, so I created a UDF (User Defined Function) and called it IsNumeric. I faced a serious problem when I wanted to use comparison operators.

 

My data was as follows

 

COL1

COL2

1

12

2

Absent

3

5

4

7 Days

5

7

6

Six Days

7

 

8

Un Aavalable

9

6

 

To create the scenario,  follow the steps below.

 

1. Create the Test1 table.

 

And executed the following command

 

create table test1(

  col1 numeric(5) primary key,

  col2 varchar(20)

 )

 

2. Insert data into the table.

 

insert into test1 values (1, '12');

insert into test1 values (2, 'Absent');

insert into test1 values (3, '5');

insert into test1 values (4, '7 Days');

insert into test1 values (5, '7');

insert into test1 values (6, 'Six Days');

insert into test1 values (7, '');

insert into test1 values (8, 'Un Avalable');

insert into test1 values (9, '6');

 

3. Create a user defined function.

 

 

create or replace function isnumeric(v in varchar2) return number as

   -- returns 1  if the parameter is numeric

   -- 0 non numeric

   num   number;

begin

   num := to_number(v);

   return 1;

exception

   when others then

      return 0;

end;

 

3. Enter your select statement.

 

select *

from test1

where isnumeric(col2) = 1

and col2 > 5

 

 

You will get the following error message.

 

ORA-01722: invalid number

 

To solve this problem

 

1. Create a ToNumeric function

 

create or replace function tonumeric(v in varchar2) return number as

   -- return value if its numeric

   -- 0 non numeric

   num   number;

begin

   num := to_number(v);

   return num;

exception

   when others then

      return null;

end;

 

2. Change the SQL statement this structure

 

select *

from test1

where isnumeric(col2) = 1

and tonumeric(col2) > 5

 

 

COL1

COL2

1

12

5

7

9

6

 

Posted on Friday, July 25, 2008 1:08 AM | Back to top


Comments on this post: Oracle Data Conversion: ORA-01722: invalid number

# re: Oracle Data Conversion: ORA-01722: invalid number
Requesting Gravatar...
Thanks a lot. I spent more than 5 hours to solve this kink of problem.
Left by Chars on Jan 26, 2009 2:17 PM

# re: Oracle Data Conversion: ORA-01722: invalid number
Requesting Gravatar...
You' welcome. I'm glad the article helped.
Left by Malisa Ncube on Jan 26, 2009 6:39 PM

# re: Oracle Data Conversion: ORA-01722: invalid number
Requesting Gravatar...
Thanks a lot. I spent more than 5 hours to solve this kind of problem.
Left by Chars on Jan 27, 2009 12:39 PM

# re: Oracle Data Conversion: ORA-01722: invalid number
Requesting Gravatar...
we have an aplication running using java and oracle, it installed in the server. we have some server, and there's one server that found this error, ORA-01722. but in the other server it works just fine.
what happen? please help us.. is it possible that there's server setting that could contribute to this kind of error
Left by emma on May 23, 2011 12:24 AM

# re: Oracle Data Conversion: ORA-01722: invalid number
Requesting Gravatar...
Server setting. No.
I experienced this problem in oracle 9i, I don't know whether it is the same in the later versions.

The UDF I created enabled the select statement to function without problems. Share the sample sql statement and we see if we can help.

Left by Malisa on May 23, 2011 1:01 AM

# re: Oracle Data Conversion: ORA-01722: invalid number
Requesting Gravatar...
I'm trying to do the following insert into an oracle table using data the was loaded into an external table. It apprears the data file is correct but get the
the ORA-01722 error everytime.

INSERT /*+ APPEND */INTO PLAN_FC_SUM
(DIVISION_ID
,FOB_ID
,SUM_GROUP_ID
,DEPT_GROUP_ID
,DEPT_ID
,SUPER_CAT_ID
,CAT_ID
,LOC_DIVISION_ID
,SALES_WEEK_ID
,WEEKS_OF_SUPPLY)
SELECT /* parallel(a,2) */
'0'||SUBSTR(field_1,7) div,
CASE when fob_id is null then '000' else fob_id END,
CASE when sum_group_id is null then '000' else sum_group_id END,
CASE when dept_group_id is null then '000' else dept_group_id END,
SUBSTR(field_2,4,3) dept, super_cat_id,
'00'||SUBSTR(field_2,10) cat,
'0'||SUBSTR(field_1,7) d,
SUBSTR(field_3,2) w,
TO_CHAR(field_4) wos
FROM T_CAT_WOS a, category b
WHERE cat_id = '00'||SUBSTR(field_2,10) AND
dept_id =SUBSTR(field_2,4,3) AND
division_id = '0'||SUBSTR(field_1,7);
COMMIT;

T_CAT_WOS file

CREATE TABLE DSP7.T_CAT_WOS
(
FIELD_1 VARCHAR2(30 BYTE),
FIELD_2 VARCHAR2(30 BYTE),
FIELD_3 VARCHAR2(30 BYTE),
FIELD_4 NUMBER
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY DOWNLOAD
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
NOBADFILE
LOGFILE 'EXTLOG':'t_cat_wos.log_xt'
FIELDS TERMINATED BY ','
)
LOCATION (DOWNLOAD:'dss_cat_wos_afl.dat')
)

Sample Date from T_CAT_WOS table.
AFLDIV24 AFL041CAT300 W201101 57.44
AFLDIV24 AFL041CAT300 W201102 56.28
AFLDIV24 AFL041CAT300 W201103 56.68
AFLDIV24 AFL041CAT300 W201104 55.51
AFLDIV24 AFL041CAT300 W201105 54.34
AFLDIV24 AFL041CAT300 W201106 54.97
AFLDIV24 AFL041CAT300 W201107 53.78
AFLDIV24 AFL041CAT300 W201108 52.63
AFLDIV24 AFL041CAT300 W201109 51.46

Error

Table truncated.

INSERT /*+ APPEND */INTO PLAN_FC_SUM
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-01722: invalid number
ORA-06512: at "SYS.ORACLE_LOADER", line 52

Left by Mike on Sep 17, 2011 6:37 AM

# re: Oracle Data Conversion: ORA-01722: invalid number
Requesting Gravatar...
This is just normal behavior.
What happens is that oracle db apply's an implicit to_number conversion on col2, so the line "and col2 > 5" becomes "and to_number(col2) > 5". Oracle always tries to make both values of the same data type.
If this is not what you want, than you should consider using "and col2 > to_char(5)"
Left by Swarudi on Dec 06, 2011 12:45 AM

# re: Oracle Data Conversion: ORA-01722: invalid number
Requesting Gravatar...
call RegistereTNims_200_234.C_ConnectionRing_Power('38.352_10.325','radio_101_201');
Left by abera on Jan 13, 2012 12:06 AM

# re: Oracle Data Conversion: ORA-01722: invalid number
Requesting Gravatar...
SQL> SELECT profile,
limit
FROM dba_profiles,
2 3 4 (SELECT Decode(limit, 'UNLIMITED', 1000,
5 limit) AS default_pass_life_time
6 FROM dba_profiles
7 WHERE profile = 'DEFAULT'
AND resource_name = 'PASSWORD_LIFE_TIME')
8 9 WHERE resource_name = 'PASSWORD_LIFE_TIME'
10 AND profile != 'DEFAULT'
11 AND To_number(Decode(limit, 'DEFAULT', default_pass_life_time,
12 'UNLIMITED', 1000,
13 limit)) > 365
14 ;
limit)) > 365
*
ERROR at line 13:
ORA-01722: invalid number
Left by Surf on Feb 05, 2014 4:46 PM

# re: Oracle Data Conversion: ORA-01722: invalid number
Requesting Gravatar...
Could any of you please help on the above query?

Thanks in Advance
Left by Surf on Feb 05, 2014 4:49 PM

# re: Oracle Data Conversion: ORA-01722: invalid number
Requesting Gravatar...
how to remove all the spaces in 'O R A C L E' by using sql queries
Left by NIDHI TIWARI on Jul 28, 2017 6:42 AM

Your comment:
 (will show your gravatar)


Copyright © Malisa L. Ncube | Powered by: GeeksWithBlogs.net