Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01722
ORA-01722 [message #193656] Mon, 18 September 2006 15:51 Go to next message
ozzy80
Messages: 55
Registered: June 2005
Member
Hello,

I am not sure what I am doing wrong...

sql>desc tab1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ITEM                                      NOT NULL VARCHAR2(18 CHAR)
 LOC                                       NOT NULL VARCHAR2(6 CHAR)
 ITEMSKUSW                                          NUMBER(3)
 PARTSTATUS                                         VARCHAR2(72 CHAR)
sql>

where I use a decode statement in the sql to insert into the above table...

DECODE(s.partstatus_hd, 'D', 'Obsolete', '2', 'Pending Obsolete', 'Active') partstatus


oracle returns...

DECODE(s.partstatus_hd, 'D', 'Obsolete', '2', 'Pending Obsolete', 'Active') partstatus,
    *
ERROR at line 6:
ORA-01722: invalid number


The error says I am trying to convert a string to a number... but the decode statement returns a string not a number... am I wrong?

Plz help!

Thanks
Re: ORA-01722 [message #193660 is a reply to message #193656] Mon, 18 September 2006 16:24 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member
partstatus_hd is not a column in table tab1 as per your describe .

What is the structure of the table you are selecting from ?

Srini
Re: ORA-01722 [message #193673 is a reply to message #193660] Mon, 18 September 2006 20:45 Go to previous messageGo to next message
ozzy80
Messages: 55
Registered: June 2005
Member
The structure of the table I am selecting from...


sql> desc selection_tab

Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ITEM                                      NOT NULL VARCHAR2(18 CHAR)
 LOC                                       NOT NULL VARCHAR2(6 CHAR)
 PARTSTATUS_HD                                         VARCHAR2(72 CHAR)
sql>


Re: ORA-01722 [message #193690 is a reply to message #193673] Tue, 19 September 2006 00:27 Go to previous messageGo to next message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

Plz Post your full insert query.

Sandy
Re: ORA-01722 [message #193798 is a reply to message #193673] Tue, 19 September 2006 08:03 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
ozzy80 wrote on Mon, 18 September 2006 21:45

The structure of the table I am selecting from...


sql> desc selection_tab

Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ITEM                                      NOT NULL VARCHAR2(18 CHAR)
 LOC                                       NOT NULL VARCHAR2(6 CHAR)
 PARTSTATUS_HD                                         VARCHAR2(72 CHAR)
sql>





What kind of describe shows TYPEs as VARCHAR2(10 CHAR)? I have never seen this before. Are you just typing this in? If so, please just cut an paste exact describe and exact select statement and exact error. No improvisation.
icon4.gif  Re: ORA-01722 [message #193833 is a reply to message #193798] Tue, 19 September 2006 10:38 Go to previous messageGo to next message
ozzy80
Messages: 55
Registered: June 2005
Member
Okay, got it...

In my sql query columns were not in sync with the table column list sequence... I rearranged the sequence and thinks are working fine...

Thanks guyz...

Ozzy

[Updated on: Tue, 19 September 2006 11:10]

Report message to a moderator

Re: ORA-01722 [message #193877 is a reply to message #193833] Tue, 19 September 2006 15:38 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
@Joy Division

VARCHAR2(18 CHAR) -> I've never seen it either; TOAD's table creation script would show VARCHAR2(18 BYTE); Oracle Raptor (don't have SQL Developer here) shows simple VARCHAR2(18). OP's output *suggests* that this is an exact output, but - where does it come from?
Re: ORA-01722 [message #194018 is a reply to message #193877] Wed, 20 September 2006 07:57 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
I just checked SQL Developer. Oddly, it doesn't seem to be like Raptor. It shows it the same way that you say TOAD does (I don't use TOAD). But what made me bring up the original question was that OP showed a DESC command, so I assumed it was sqlplus as I don't know anything else that allows for that command. It's also odd that the SQL prompt was in lower case. Sure, you can change the prompt to be anything, but why go through all that trouble just to change the case of the prompt.

That's what made me think this wasn't a true cut and paste, but more of a typing on the fly.
Previous Topic: General Question regarding High Water Mark
Next Topic: Bulk Fetch
Goto Forum:
  


Current Time: Sat Dec 10 13:05:27 CST 2016

Total time taken to generate the page: 0.08136 seconds