Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01722: invalid number in 10g (10g)
ORA-01722: invalid number in 10g [message #360886] Mon, 24 November 2008 04:33 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I am encountering problem when I tried to join a Character with Number Datatype,
In Oracle 9i its working fine.But in 10g, I am getting "ORA-01722: invalid number" error.




Column COL1 in table has a data mixture of number as well as character. The data type for COL1 is char(5). ACC_NBR is NUMBER(5)

SELECT..
(select C_ID
            from ACC_DTL B
            where B.C_TYPE = 'D'
            and COL1 = A.ACC_NBR)
			...
			



Regards,
Oli

[Updated on: Mon, 24 November 2008 04:36]

Report message to a moderator

Re: ORA-01722: invalid number in 10g [message #360887 is a reply to message #360886] Mon, 24 November 2008 04:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is surprising you?
It is normal.

Regards
Michel
Re: ORA-01722: invalid number in 10g [message #360889 is a reply to message #360887] Mon, 24 November 2008 04:43 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks for the reply.
Quote:

What is surprising you?




Quote:

In Oracle 9i its working fine.But in 10g, I am getting "ORA-01722: invalid number" error!




Regards,
Oli

[Updated on: Mon, 24 November 2008 04:44]

Report message to a moderator

Re: ORA-01722: invalid number in 10g [message #360890 is a reply to message #360886] Mon, 24 November 2008 04:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try joining on
and COL1 = to_char(A.ACC_NBR)

Re: ORA-01722: invalid number in 10g [message #360892 is a reply to message #360890] Mon, 24 November 2008 04:48 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Yes I did that. But does in oracle 10g implicit conversion not happens? And also want to know if there is any other issue with 10g like this.


Thanks for giving your time

Regards,
Oli

[Updated on: Mon, 24 November 2008 04:51]

Report message to a moderator

Re: ORA-01722: invalid number in 10g [message #360895 is a reply to message #360892] Mon, 24 November 2008 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is implicit conversion in 10g, maybe not the same one than in 9i maybe the operations are not done in the same order and so on.

Regards
Michel
Re: ORA-01722: invalid number in 10g [message #360907 is a reply to message #360895] Mon, 24 November 2008 05:47 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Had a concern. Because if this is the issue (if in 10g its different the way it handled in 9i) then one needs to modify sqls in 10g.

Thanks ...

Regards,
Oli
Re: ORA-01722: invalid number in 10g [message #360924 is a reply to message #360907] Mon, 24 November 2008 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The thing is that the query was already wrong in 9i but it worked by chance.
So, yes, all SQL should be reviewed.

Regards
Michel
Re: ORA-01722: invalid number in 10g [message #360932 is a reply to message #360924] Mon, 24 November 2008 07:00 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
And also it's being insisted to make conversation explicitely Thanks for giving your time.


Want to add a note that in 10g, if COL1 is of varchar2 then the error doesn't raised.


Regards,
Oli

[Updated on: Mon, 24 November 2008 07:03]

Report message to a moderator

Re: ORA-01722: invalid number in 10g [message #360936 is a reply to message #360932] Mon, 24 November 2008 07:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think you're misunderstanding the problem @Olivia.

This isn't a new problem that 10g has introduced - you can get problems like this quite easily in 9i, 8i, or 7.

Your query always had an error in it in that you were joining columns of different data types without providing an explicit conversion - Would you join a date and a varchar2 without using a to_date or to_char?

All that's happened is that the 10g optimiser is chosing to process the query in a different way, and the error is now coming to light.
Re: ORA-01722: invalid number in 10g [message #360942 is a reply to message #360907] Mon, 24 November 2008 07:19 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@Olivia,

I was a bit confused with this post here. I tried a few queries in my 9i machine and I seem to have replicated the error you claimed was not coming up in Oracle 9i.

"From Oracle Documentation"

During arithmetic operations on and comparisons between character and noncharacter datatypes, Oracle converts from any character datatype to a number, date, or rowid, as appropriate. In arithmetic operations between CHAR/VARCHAR2 and NCHAR/NVARCHAR2, Oracle converts to a number.


SQL> SELECT banner
  2    FROM v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for 32-bit Windows: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

5 rows selected.

SQL>   
SQL> DROP TABLE test_tab_1;
DROP TABLE test_tab_1
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> 
SQL> DROP TABLE test_tab_2;
DROP TABLE test_tab_2
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> 
SQL> CREATE TABLE test_tab_1
  2  (
  3  col_1 NUMBER(2)
  4  );

Table created.

SQL> 
SQL> CREATE TABLE test_tab_2
  2  (
  3  col_2 CHAR(2)
  4  );

Table created.

SQL> 
SQL> INSERT INTO test_tab_1
  2       VALUES (10);

1 row created.

SQL> 
SQL> INSERT INTO test_tab_1
  2       VALUES (20);

1 row created.

SQL> 
SQL> INSERT INTO test_tab_2
  2       VALUES ('10');

1 row created.

SQL> 
SQL> INSERT INTO test_tab_2
  2       VALUES ('30');

1 row created.

SQL> 
SQL> INSERT INTO test_tab_2
  2       VALUES ('1A');

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT * FROM test_tab_1;

     COL_1
----------
        10
        20

2 rows selected.

SQL>  SELECT * FROM test_tab_2;

CO
--
10
30
1A

3 rows selected.

SQL> SELECT a1.col_1, a2.col_2
  2  from test_tab_1 a1, test_tab_2 a2
  3  where a1.col_1 = a2.col_2;
where a1.col_1 = a2.col_2
                 *
ERROR at line 3:
ORA-01722: invalid number


SQL>  DELETE FROM test_tab_2 WHERE col_2 = '1A';

1 row deleted.

SQL>  SELECT a1.col_1, a2.col_2
  2   from test_tab_1 a1, test_tab_2 a2
  3   where a1.col_1 = a2.col_2;

     COL_1 CO
---------- --
        10 10

1 row selected.

SQL> drop table test_tab_1;

Table dropped.

SQL> drop table test_tab_2;

Table dropped.


I ran the same query in 10g and got the same results as expected. So I guess you are not running the same code in 9i and 10g.
[***Added: Forgot the documentation link. Search for Datatype Comparison Rules in this link ]

Regards,
Jo

[Updated on: Mon, 24 November 2008 07:23]

Report message to a moderator

Re: ORA-01722: invalid number in 10g [message #360945 is a reply to message #360936] Mon, 24 November 2008 07:21 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks for the update Jrowbottom.


I have already mentioned that in my earlier post

Quote:


it's being insisted to make conversation explicitely



Regards,
Oli
Re: ORA-01722: invalid number in 10g [message #360952 is a reply to message #360892] Mon, 24 November 2008 07:50 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Olivia wrote on Mon, 24 November 2008 05:48
And also want to know if there is any other issue with 10g like this.



I don't mean to sound cruel, but if you coded properly, you wouldn't have to worry about what happens between versions. I'd go so far as to say it is a bug in 9i allowing that type of join.
Re: ORA-01722: invalid number in 10g [message #360973 is a reply to message #360952] Mon, 24 November 2008 08:51 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks for the suggesion Joy. While analyzing code I found this issue. I agree that explicit conversion is required.I even maintain that. But unfortunately I got the sql and wanted to clear my doubts.

Previous Topic: Pagesize and performance
Next Topic: Date Bug in Oracle ?
Goto Forum:
  


Current Time: Sat Dec 03 00:55:04 CST 2016

Total time taken to generate the page: 0.19737 seconds