OraFAQ Forum: SQL & PL/SQL » UTL_RAW.CAST_TO_NUMBER PL/SQL: numeric or value error

Home » SQL & PL/SQL » SQL & PL/SQL » UTL_RAW.CAST_TO_NUMBER PL/SQL: numeric or value error (Oracle 10G windows )

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

UTL_RAW.CAST_TO_NUMBER PL/SQL: numeric or value error [message #573852]

Thu, 03 January 2013 11:05

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

Report message to a moderator

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

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

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

Report message to a moderator

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

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) ;

Report message to a moderator

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

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

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

Report message to a moderator

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

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

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

Report message to a moderator

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

skumari
Messages: 7
Registered: January 2013
Location: NY

Junior Member

Hi Michel,
This URL is askinh username and pswword.

Report message to a moderator

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

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.

Report message to a moderator

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

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

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

Report message to a moderator

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

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

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.

Report message to a moderator

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

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.

Report message to a moderator

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

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

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

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

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.

Report message to a moderator

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

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

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 03 22:19:19 CST 2025