Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502 with UTL_RAW (11gr2, Windows)
ORA-06502 with UTL_RAW [message #657123] Fri, 28 October 2016 07:12 Go to next message
qazims92
Messages: 37
Registered: July 2016
Location: Lahore Pakistan
Member
Hi Experts,

When we execute following command on 11.2.0.1, we get result.


But when we execute same command on 11.2.0.4.0 then we get ORA-06502.



select UTL_RAW.cast_to_number(rawtohex(to_char(trim('JSC010')))) from dual;

Kindly guide.

Regards,
Qazi
Re: ORA-06502 with UTL_RAW [message #657127 is a reply to message #657123] Fri, 28 October 2016 07:23 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
qazims92 wrote on Fri, 28 October 2016 05:12
Hi Experts,

When we execute following command on 11.2.0.1, we get result.


But when we execute same command on 11.2.0.4.0 then we get ORA-06502.



select UTL_RAW.cast_to_number(rawtohex(to_char(trim('JSC010')))) from dual;

Kindly guide.

Regards,
Qazi
06502, 00000, "PL/SQL: numeric or value error%s"
// *Cause: An arithmetic, numeric, string, conversion, or constraint error
//         occurred. For example, this error occurs if an attempt is made to
//         assign the value NULL to a variable declared NOT NULL, or if an
//         attempt is made to assign an integer larger than 99 to a variable
//         declared NUMBER(2).   
// *Action: Change the data, how it is manipulated, or how it is declared so
//          that values do not violate constraints. 

DUAL is defined as being only 1 character wide.
Re: ORA-06502 with UTL_RAW [message #657128 is a reply to message #657123] Fri, 28 October 2016 07:25 Go to previous messageGo to next message
John Watson
Messages: 7262
Registered: January 2010
Location: Global Village
Senior Member
I surprised it ever worked, because it is full of bugs. You are passing a string to to_char instead of a date or number; a string to rawtohex when it should be a raw; a string to utl_raw.cast_to_number when it should be a raw.
Re: ORA-06502 with UTL_RAW [message #657129 is a reply to message #657127] Fri, 28 October 2016 07:33 Go to previous messageGo to next message
qazims92
Messages: 37
Registered: July 2016
Location: Lahore Pakistan
Member
Thanks Blackswan for your response.

But same statement is working on Oracle 11.2.0.1.0 .

Regards,
Qazi
Re: ORA-06502 with UTL_RAW [message #657130 is a reply to message #657129] Fri, 28 October 2016 07:34 Go to previous messageGo to next message
John Watson
Messages: 7262
Registered: January 2010
Location: Global Village
Senior Member
So it is a bug that has been fixed.
Re: ORA-06502 with UTL_RAW [message #657131 is a reply to message #657129] Fri, 28 October 2016 07:35 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
qazims92 wrote on Fri, 28 October 2016 05:33
Thanks Blackswan for your response.

But same statement is working on Oracle 11.2.0.1.0 .

Regards,
Qazi
So what does above prove?

You must deal with reality as it exist TODAY.
Re: ORA-06502 with UTL_RAW [message #657132 is a reply to message #657123] Fri, 28 October 2016 07:37 Go to previous messageGo to next message
pablolee
Messages: 2847
Registered: May 2007
Location: Scotland
Senior Member
Out of interest, what problem are you actually trying to solve with this code?
Re: ORA-06502 with UTL_RAW [message #657199 is a reply to message #657132] Tue, 01 November 2016 02:56 Go to previous messageGo to next message
qazims92
Messages: 37
Registered: July 2016
Location: Lahore Pakistan
Member
Hi Expert,

I created a test table with a column name VAL having data type CHAR and size 9.

When I execute following command I get error ORA-06502:

select UTL_RAW.cast_to_number(rawtohex(to_char(val))) AS Sort_Dealer from test;

But I increase column width to 21 I got result.

SQL> select UTL_RAW.cast_to_number(rawtohex(to_char(val))) AS Sort_Dealer fro
m test;

SORT_DEALER
-----------
-1.835E-23

Is there any way that I can see same result without increasing column width for example by changing output settings.

Thanks & Regards,
Qazi
Re: ORA-06502 with UTL_RAW [message #657200 is a reply to message #657199] Tue, 01 November 2016 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
But I increase column width to 21 I got result.
Which column?
Post a test case we can reproduce: create table and insert statements.
Use SQL*Plus and copy and paste your session.
Before read How to use [code] tags and make your code easier to read.

Re: ORA-06502 with UTL_RAW [message #657201 is a reply to message #657199] Tue, 01 November 2016 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I created a test table with a column name VAL having data type CHAR and size 9.
Quote:
to_char(val)
What do you think about applying TO_CHAR on a CHAR value?

Re: ORA-06502 with UTL_RAW [message #657202 is a reply to message #657199] Tue, 01 November 2016 03:28 Go to previous messageGo to next message
pablolee
Messages: 2847
Registered: May 2007
Location: Scotland
Senior Member
Hmmm, you didn't actually answer the question.

What are you actually trying to do? What problem are you trying to solve?

Are you just 'playing' with data to see what you can and cannot do with it? If so, fair play, but you have already had your answer in terms of what is wrong with your code.
Do you have an issue with data that you have in your database? If so, describe the issue, post a test case, tell us what is needed to resolve the issue and someone will most likely be able to assist.
Re: ORA-06502 with UTL_RAW [message #657212 is a reply to message #657199] Tue, 01 November 2016 14:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8805
Registered: November 2002
Location: California, USA
Senior Member
You can cast to char(21) instead of using to_char, as shown below, whether selecting from dual or a user table.

-- selecting from dual with to_char, then with cast to char(21):
SCOTT@orcl_12.1.0.2.0> select UTL_RAW.cast_to_number(rawtohex(to_char(trim('JSC010')))) from dual
  2  /
select UTL_RAW.cast_to_number(rawtohex(to_char(trim('JSC010')))) from dual
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.UTL_RAW", line 388


SCOTT@orcl_12.1.0.2.0> select UTL_RAW.cast_to_number(rawtohex(cast(trim('JSC010') as char(21)))) from dual
  2  /

UTL_RAW.CAST_TO_NUMBER(RAWTOHEX(CAST(TRIM('JSC010')ASCHAR(21))))
----------------------------------------------------------------
                                                      -1.835E-23

1 row selected.

-- selecting from user table with column of char(9) with to_char, then with cast to char(21):
SCOTT@orcl_12.1.0.2.0> create table test (val char(9))
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> insert into test (val) values ('JSC010')
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> select UTL_RAW.cast_to_number(rawtohex(to_char(val))) AS Sort_Dealer from test
  2  /
select UTL_RAW.cast_to_number(rawtohex(to_char(val))) AS Sort_Dealer from test
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.UTL_RAW", line 388


SCOTT@orcl_12.1.0.2.0> select UTL_RAW.cast_to_number(rawtohex(to_char(cast(val as char(21))))) AS Sort_Dealer from test
  2  /

SORT_DEALER
-----------
 -1.835E-23

1 row selected.
Re: ORA-06502 with UTL_RAW [message #657228 is a reply to message #657212] Wed, 02 November 2016 02:29 Go to previous message
qazims92
Messages: 37
Registered: July 2016
Location: Lahore Pakistan
Member
Thanks Barbara for your kind and valueable response.

Regards,
Qazi
Previous Topic: Partition Removing
Next Topic: Default Character type of the column
Goto Forum:
  


Current Time: Fri Feb 23 00:52:39 CST 2018

Total time taken to generate the page: 0.03148 seconds