Home » SQL & PL/SQL » SQL & PL/SQL » UTL_RAW.CAST_TO_NUMBER PL/SQL: numeric or value error (Oracle 10G windows )
UTL_RAW.CAST_TO_NUMBER PL/SQL: numeric or value error [message #573852] Thu, 03 January 2013 11:05 Go to next message
skumari
Messages: 7
Registered: January 2013
Location: NY
Junior Member
Hi All,
Please help me to resolve the issue:
I am trying to convert RAW datatype to number, it gives me numeric or value error.

Here is my query.

select UTL_RAW.CAST_TO_NUMBER (UTL_RAW.BIT_AND(UTL_RAW.CAST_FROM_NUMBER(174358600),UTL_RAW.CAST_FROM_NUMBER(16777215)) ) from dual;

-- below query works fine and gives me C4004A0010 as result
select UTL_RAW.BIT_AND(UTL_RAW.CAST_FROM_NUMBER(174358600),UTL_RAW.CAST_FROM_NUMBER(16777215)) bitand from dual;

--This below query gives error as
select UTL_RAW.CAST_TO_NUMBER('C4004A0010') from dual

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.UTL_RAW", line 388
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:

Please advise how can i convert to number from RAW DATA Type


Re: UTL_RAW.CAST_TO_NUMBER PL/SQL: numeric or value error [message #573857 is a reply to message #573852] Thu, 03 January 2013 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 58607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
UTL_RAW.CAST_FROM_NUMBER returns the internal representation of the number.
When you BITAND both these representation, you do not bitand the number value, and in the end you get an invalid representation of a number and so you cannot go back using CAST_TO_NUMBER.

Regards
Michel
Re: UTL_RAW.CAST_TO_NUMBER PL/SQL: numeric or value error [message #573861 is a reply to message #573852] Thu, 03 January 2013 11:51 Go to previous messageGo to next message
skumari
Messages: 7
Registered: January 2013
Location: NY
Junior Member
Thank you very much for your reply.

Then how can i convert BitAND value to a number. Is there any way to convert the bit and result to a number ?
I have to use that in further logical calculations, some thing like below.
------
v_temp := UTL_RAW.CAST_TO_NUMBER (UTL_RAW.BIT_AND(UTL_RAW.CAST_FROM_NUMBER(v_ip),UTL_RAW.CAST_FROM_NUMBER(16777215)) );
v_oct4 := MOD(v_temp, 256) ;
v_oct3 := MOD((v_temp / 256), 256) ;
Re: UTL_RAW.CAST_TO_NUMBER PL/SQL: numeric or value error [message #573862 is a reply to message #573861] Thu, 03 January 2013 12:01 Go to previous messageGo to next message
Michel Cadot
Messages: 58607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select to_char(174358600,'fm0000000X') v1 from dual;
V1
---------
0A648048

1 row selected.

SQL> select to_char(16777215,'fm0000000X') v2 from dual;
V2
---------
00FFFFFF

1 row selected.

SQL> select utl_raw.bit_and(to_char(174358600,'fm0000000X'),to_char(16777215,'fm0000000X')) "BITAND" from dual;
BITAND
------------------------------------------------------------------------------------------------------------------------
00648048

1 row selected.

SQL> select to_number(utl_raw.bit_and(to_char(174358600,'fm0000000X'),to_char(16777215,'fm0000000X')),
  2                   'XXXXXXXX')"BITAND" 
  3  from dual;
    BITAND
----------
   6586440

1 row selected.

Regards
Michel
Re: UTL_RAW.CAST_TO_NUMBER PL/SQL: numeric or value error [message #574290 is a reply to message #573862] Tue, 08 January 2013 11:14 Go to previous messageGo to next message
Michel Cadot
Messages: 58607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I forgot it but I once wrote a package to work on bits.
You'll find it at: http://www.dba-village.com/village/dvp_scripts.ScriptDetails?ScriptIdA=2285

Note that it is fair and appreciated you feedback, if not thank, to people that help you.
Note that the opposite is seen as rude and will just lead you will no more have help from this site.

Regards
Michel
Re: UTL_RAW.CAST_TO_NUMBER PL/SQL: numeric or value error [message #574479 is a reply to message #574290] Thu, 10 January 2013 14:45 Go to previous messageGo to next message
skumari
Messages: 7
Registered: January 2013
Location: NY
Junior Member
Hi Michel,
This URL is askinh username and pswword.


Re: UTL_RAW.CAST_TO_NUMBER PL/SQL: numeric or value error [message #574480 is a reply to message #574479] Thu, 10 January 2013 14:46 Go to previous messageGo to next message
skumari
Messages: 7
Registered: January 2013
Location: NY
Junior Member
Hi Michel,

For -ve numbers this query gives me Invalid Hex number error.
SELECT to_number(utl_raw.bit_and(to_char(-1407252713,'fm0000000X'),to_char(16777215,'fm0000000X')),'XXXXXXXX') FROM DUAL ;

Please can you help me fixing this.
Re: UTL_RAW.CAST_TO_NUMBER PL/SQL: numeric or value error [message #574482 is a reply to message #574479] Thu, 10 January 2013 14:56 Go to previous messageGo to next message
Michel Cadot
Messages: 58607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
skumari wrote on Thu, 10 January 2013 21:45
Hi Michel,
This URL is askinh username and pswword.


So register.

Regards
Michel

Re: UTL_RAW.CAST_TO_NUMBER PL/SQL: numeric or value error [message #574486 is a reply to message #573852] Thu, 10 January 2013 18:17 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1962
Registered: January 2010
Senior Member
skumari wrote on Thu, 03 January 2013 12:05

-- below query works fine and gives me C4004A0010 as result
select UTL_RAW.BIT_AND(UTL_RAW.CAST_FROM_NUMBER(174358600),UTL_RAW.CAST_FROM_NUMBER(16777215)) bitand from dual;


What are you trying to achieve? Decimal 174358600 is HEX A648048. Decimal 16777215 is HEX FFFFFF. Bitand betwwen the two would be HEX 648048 (decimal 6586440), not HEX C4004A0010. All you need it to use plain bitand:

SQL> select bitand(174358600,16777215) from dual;

BITAND(174358600,16777215)
--------------------------
                   6586440


SY.
Re: UTL_RAW.CAST_TO_NUMBER PL/SQL: numeric or value error [message #574525 is a reply to message #574486] Fri, 11 January 2013 12:16 Go to previous messageGo to next message
skumari
Messages: 7
Registered: January 2013
Location: NY
Junior Member
Hi ,

For -ve numbers this query gives me Invalid Hex number error.
SELECT to_number(utl_raw.bit_and(to_char(-1407252713,'fm0000000X'),to_char(16777215,'fm0000000X')),'XXXXXXXX') FROM DUAL ;

Please can you help me fixing this.
Re: UTL_RAW.CAST_TO_NUMBER PL/SQL: numeric or value error [message #574526 is a reply to message #574525] Fri, 11 January 2013 12:23 Go to previous messageGo to next message
Michel Cadot
Messages: 58607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you try Solomon's solution (althought this function is internal and not documented)?
Did you try the package I pointed you to?
Did you try to fix the query by yourself?

What are the bits for -1407252713?

Regards
Michel

[Updated on: Fri, 11 January 2013 12:25]

Report message to a moderator

Re: UTL_RAW.CAST_TO_NUMBER PL/SQL: numeric or value error [message #574528 is a reply to message #574526] Fri, 11 January 2013 13:12 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1962
Registered: January 2010
Senior Member
Michel Cadot wrote on Fri, 11 January 2013 13:23
Did you try Solomon's solution (althought this function is internal and not documented)?


BITAND is documented since 8i.

SY.
Re: UTL_RAW.CAST_TO_NUMBER PL/SQL: numeric or value error [message #574530 is a reply to message #574528] Fri, 11 January 2013 14:04 Go to previous message
Michel Cadot
Messages: 58607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Doh! I mix up with REVERSE... otherwise I will put it in my first answer instead of this b... expression with utl_raw.

Regards
Michel
Previous Topic: Error "ORA-00904" while using subquery.
Next Topic: Adding an IF Statement to a Trigger?
Goto Forum:
  


Current Time: Tue Jul 29 04:21:49 CDT 2014

Total time taken to generate the page: 0.06544 seconds