Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01722
ORA-01722 [message #210789] Fri, 22 December 2006 06:49 Go to next message
impemp
Messages: 3
Registered: December 2006
Location: india
Junior Member
Hi Friends,
i am getting following error even though my table's columns are pure varchar2 columns and trying to insert character values only. please help me to solve this.
following are the details.

[COLOR=deeppink]desc station_tab
Name Type Nullable Default Comments
------- ------------ -------- ------- --------
STNCODE VARCHAR2(5) Y
STNNAME VARCHAR2(20) Y
ZONE VARCHAR2(5) Y
JN VARCHAR2(5) Y

SQL> insert into station_tab values('TPTY','TIRUPATHI','SCR','N');

insert into station_tab values('TPTY','TIRUPATHI','SCR','N')

ORA-01722: invalid number
Re: ORA-01722 [message #210792 is a reply to message #210789] Fri, 22 December 2006 06:58 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oracle doesn't know which value goes to which column and, obviously, tried to store 'TIRUPATHI' into one of VARCHAR2(5) columns.

When inserting, ALWAYS use column list specification:
insert into station_tab 
  (stncode, stnname, zone, jn)
   values
  ('TPTY', 'TIRUPATHI', 'SCR', 'N');

Re: ORA-01722 [message #210796 is a reply to message #210789] Fri, 22 December 2006 07:25 Go to previous messageGo to next message
impemp
Messages: 3
Registered: December 2006
Location: india
Junior Member
Thank you, now problem is resolved
Re: ORA-01722 [message #210818 is a reply to message #210792] Fri, 22 December 2006 08:36 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Wow, has something changed in 10g, because in my sloppy old days, if you didn't name the columns it would just insert in the order of the columns in the table?
I am glad if that's the case to force people to make better code. Like the way group by used to do an implicit order by and now it doesn't. Now if we can only get Oracle to report an error if one tries to use strings as DATEs.
Re: ORA-01722 [message #210824 is a reply to message #210789] Fri, 22 December 2006 08:45 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
I believe the query would have worked without the "VALUES" clause:

insert into station_tab('TPTY','TIRUPATHI','SCR','N');


However, since VALUES was specified, the columns needed to be referenced as well. Besides, it's better to specify which columns each value should go into. Why take a chance that something didn't get put in the proper column, especially with the somewhat ambiguous and often confusing error messages Oracle provides!
Re: ORA-01722 [message #210833 is a reply to message #210824] Fri, 22 December 2006 10:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry, but I must be missing something here.
I would say there HAS to be a trigger involved. How else would you get an Invalid number?
That would mean that the VALUES clause had nothing (or little) to do with the actual error.
Re: ORA-01722 [message #210840 is a reply to message #210833] Fri, 22 December 2006 11:11 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi Frank,

Joy is right, is it feature of 10g to give the column name also please clear my doubt . Because if you didn't name the columns it would just insert in the order of the columns in the table?

Bye
Ashu
Re: ORA-01722 [message #210843 is a reply to message #210840] Fri, 22 December 2006 11:18 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I agree with Frank, there must be a trigger on that table:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

SQL> create table station_tab (
  2  stncode varchar2(5),
  3  stnname varchar2(20),
  4  zone varchar2(5),
  5  jn varchar2(5));

Table created.

SQL>  insert into station_tab values('TPTY','TIRUPATHI','SCR','N');

1 row created.

SQL> select * from station_tab;

STNCO STNNAME              ZONE  JN
----- -------------------- ----- -----
TPTY  TIRUPATHI            SCR   N
Re: ORA-01722 [message #210864 is a reply to message #210833] Fri, 22 December 2006 13:21 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Frank wrote on Fri, 22 December 2006 11:34
Sorry, but
I would say there HAS to be a trigger involved. How else would you get an Invalid number?



Good catch. I missed that one, but I am wondering how Littlefoot's solution would have fixed this problem, unless OP didn't use Littlefoot's suggestion and found some other error.
Re: ORA-01722 [message #210871 is a reply to message #210864] Fri, 22 December 2006 16:57 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I believe he didn't even try it; will find out next week that my cool solution doesn't have anything to do with the problem. I don't know what to say except - I wasn't thinking at all. Sorry.
Re: ORA-01722 [message #211117 is a reply to message #210871] Tue, 26 December 2006 08:09 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Littlefoot wrote on Fri, 22 December 2006 17:57
I don't know what to say except - I wasn't thinking at all. Sorry.


I hope you didn't interpret my posting as a knock on your solution. I meant that if your solution was used, how all of a sudden does the OP not get the error anymore? Something else must be afoot Wink
Re: ORA-01722 [message #211141 is a reply to message #211117] Tue, 26 December 2006 13:10 Go to previous message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh no! I'm to blame ./fa/1637/0/ - if I, actually, created my own test case, I would have seen that mixed columns cause ORA-12899: value too large for column, and not ORA-01722: invalid number.

But I still think that it is a good habit to name all columns in INSERT statement ./fa/1601/0/.
Previous Topic: Sending Email From Oracle
Next Topic: sql query doubt
Goto Forum:
  


Current Time: Thu Dec 08 10:08:42 CST 2016

Total time taken to generate the page: 0.08442 seconds