Home » SQL & PL/SQL » SQL & PL/SQL » Cast_to_number not working
Cast_to_number not working [message #664281] Tue, 11 July 2017 00:55 Go to next message
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
Re: Cast_to_number not working [message #664283 is a reply to message #664281] Tue, 11 July 2017 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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?

Re: Cast_to_number not working [message #664284 is a reply to message #664283] Tue, 11 July 2017 01:21 Go to previous messageGo to next message
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
Re: Cast_to_number not working [message #664285 is a reply to message #664284] Tue, 11 July 2017 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Cast_to_number not working [message #664287 is a reply to message #664285] Tue, 11 July 2017 02:00 Go to previous message
purnima1
Messages: 79
Registered: June 2014
Member
Thanks Michel Cadot for prompt reply. We need to change the datatype of existing column then.
Previous Topic: Update Match Flag Status in Invoice Distribution
Next Topic: How to retrieve negative value as positive in simple brackets?
Goto Forum:
  


Current Time: Thu Mar 28 09:46:11 CDT 2024