OraFAQ Forum: SQL & PL/SQL » Cast_to

Home » SQL & PL/SQL » SQL & PL/SQL » Cast_to_number not working

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Cast_to_number not working [message #664281]

Tue, 11 July 2017 00:55

purnima1
Messages: 79
Registered: June 2014

Member

Hi All,

I am trying to run below mentioned query in 12c

select utl_raw.cast_to_number (standard_hash ('nfjdsfjdsk') )from dual ;

But getting error :

[Error] Execution (97: 8): ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.UTL_RAW", line 388

Could you please help. how can I convert it into number because I need to store this value in number column

Report message to a moderator

Re: Cast_to_number not working [message #664283 is a reply to message #664281]

Tue, 11 July 2017 01:03

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The error is expected.
As the documentation states:
Quote:

This function casts the RAW binary representation of a NUMBER into a NUMBER.

And STANDARD_HASH has no reason to return the representation of number.

Why do you want to store the result into a number and not a RAW which is the correct type?

Report message to a moderator

Re: Cast_to_number not working [message #664284 is a reply to message #664283]

Tue, 11 July 2017 01:21

purnima1
Messages: 79
Registered: June 2014

Member

I want to store it in number because we have existing columns in lot of tables which is of number type. So looking for option if we can change it to number .

If this is not possible Then I need to change to raw everywhere

Report message to a moderator

Re: Cast_to_number not working [message #664285 is a reply to message #664284]

Tue, 11 July 2017 01:28

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This is not possible to convert it accurately.
You can create your own hash function which will return a number but read again the limit of a NUMBER.

Report message to a moderator

Re: Cast_to_number not working [message #664287 is a reply to message #664285]

Tue, 11 July 2017 02:00

purnima1
Messages: 79
Registered: June 2014

Member

Thanks Michel Cadot for prompt reply. We need to change the datatype of existing column then.

Report message to a moderator

Previous Topic:	Update Match Flag Status in Invoice Distribution
Next Topic:	How to retrieve negative value as positive in simple brackets?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 18 11:28:41 CDT 2025