Home » SQL & PL/SQL » SQL & PL/SQL » command to number
command to number [message #233515] Thu, 26 April 2007 07:59 Go to next message
bollamha
Messages: 5
Registered: April 2007
Junior Member
Hi

I am using update command in Stored procedure. In this I am assign Varchar datatype value(it is numaric value) to number data field. I tried with TO_NUMBER function. but it is giving char to number conversion error. Any one can help me.

Thanks
Hari
Re: command to number [message #233517 is a reply to message #233515] Thu, 26 April 2007 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If to_number gives an "invalid number" error that is because you don't have a number in your field.
Check all values and find all those that are not numeric.

Regards
Michel
Re: command to number [message #233518 is a reply to message #233515] Thu, 26 April 2007 08:05 Go to previous messageGo to next message
bollamha
Messages: 5
Registered: April 2007
Junior Member
I queried same data. all are numbers.. Please help me
Re: command to number [message #233522 is a reply to message #233518] Thu, 26 April 2007 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, this is wrong... or your procedure is wrong.
If Oracle tells this is not a number, then this is not a number... at least in the format Oracle thinks it is given it you don't give any.

Regards
Michel
Re: command to number [message #233523 is a reply to message #233518] Thu, 26 April 2007 08:08 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Please post a "copy-paste" from your sqlplus window.

So we can exactly see what happens.
Re: command to number [message #233524 is a reply to message #233518] Thu, 26 April 2007 08:08 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,

show us what you did. and the error message..

its hard to tell other than this with out the code..
{Edit>>> martijn>>> sorry i was a bit slow Embarassed }
regards
shanth

[Updated on: Thu, 26 April 2007 08:11]

Report message to a moderator

Re: command to number [message #233528 is a reply to message #233524] Thu, 26 April 2007 08:12 Go to previous messageGo to next message
bollamha
Messages: 5
Registered: April 2007
Junior Member
BEGIN
FOR r_data IN c_data
LOOP
UPDATE WCF_DATA
SET rn = to_number(p_rn)
WHERE l_id=r_data.l_id;

END LOOP;
COMMIT;


In above portion of code p_rn is varchar type, rn is number type

Thank you
Hari
Re: command to number [message #233529 is a reply to message #233528] Thu, 26 April 2007 08:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now post some values you have in p_rn.

Regards
Michel
Re: command to number [message #233532 is a reply to message #233515] Thu, 26 April 2007 08:19 Go to previous messageGo to next message
bollamha
Messages: 5
Registered: April 2007
Junior Member
All p_rn values are Numbers
7165
7166
etc.,

Now I have declared p_rn in the declaration part as NUMBER.
But now I am getting different error message:

ORA-01407: cannot update ("WC"."WCF_DATA"."rn") to NULL

Thank you
Hari
Re: command to number [message #233534 is a reply to message #233532] Thu, 26 April 2007 08:26 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,

Error:  	ORA-01407: cannot update ("SCHEMA"."TABLE_NAME"."COLUMN_NAME") to NULL
Cause: 	You tried to update a column to a NULL value but the column will not accept NULL values.
Action: 	The options to resolve this Oracle error are:

   1. Correct your UPDATE statement so that you do not UPDATE a column with a NULL value when the column is defined as NOT NULL.



regards,
shanth
Re: command to number [message #233535 is a reply to message #233532] Thu, 26 April 2007 08:27 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ah Ah! Your rn column is declared as NOT NULL and some values in p_rn is null.
Use NVL function to give a value when you encounter a NULL, for instance: "rn = nvl(to_number(p_rn),0)".

Regards
Michel
Previous Topic: Need help on view..
Next Topic: update a column using a column from another table
Goto Forum:
  


Current Time: Fri Dec 02 23:08:42 CST 2016

Total time taken to generate the page: 0.07992 seconds