Home » SQL & PL/SQL » SQL & PL/SQL » help with error executing a view (oracle 12c)
help with error executing a view [message #668572] |
Sat, 03 March 2018 06:19 |
|
laredoeneko
Messages: 21 Registered: November 2017
|
Junior Member |
|
|
Hello
i have one table with one column than represent a string in hex decimal format, something like this
41 52 4C 54 71 91 11 37
5A 54 45 47 C0 6F B9 83
5A 54 45 47 C8 27 9A C1
41 52 4C 54 74 41 09 12
41 52 4C 54 65 30 92 48
5A 54 45 47 C6 31 DA 3C
i have create a view to traslate the info in this field from hex to decimal format:
CONCAT (
UTL_RAW.CAST_TO_VARCHAR2 (
HEXTORAW (
SUBSTR (REPLACE (ont_serial_number, ' ', ''), 1, 8))),
SUBSTR (REPLACE (ont_serial_number, ' ', ''), 9, 17))
but in some case the view fail with error like this
SUBSTR (REPLACE (ont_serial_number, ' ', ''), 1, 8))),
*
ERROR at line 5:
ORA-01465: invalid hex number
there are a lot of rows (more than 40M), how can i find the value than have the error? something link the rownum , rowid, or something similar.?
maybe i have to create a function than make the traslation with a varchar2 as input and return the calculate value, in this case i think that i can catch the error...can you help me with this function?
best regard.
Laredo
[EDITED by LF: applied [code] tags]
[Updated on: Sat, 03 March 2018 10:34] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|
|
Re: help with error executing a view [message #668581 is a reply to message #668580] |
Sat, 03 March 2018 14:35 |
|
laredoeneko
Messages: 21 Registered: November 2017
|
Junior Member |
|
|
sorry, here you have an example:
DROP TABLE ERROR CASCADE CONSTRAINTS;
CREATE TABLE ERROR
(
serial VARCHAR2(200 BYTE),
NAME NUMBER
);
Insert into ERROR
(serial, NAME)
Values
('5A 54 45 47 C1 BE E1 DF', 259.383430473067);
Insert into ERROR
(serial, NAME)
Values
('5A 54 45 47 C1 BE E1 DF', 309.375919199647);
Insert into ERROR
(serial, NAME)
Values
('5A 54 45 47 C1 BE E1 DF', 562.557831249767);
Insert into ERROR
(serial, NAME)
Values
('5A 54 45 47 C1 BE E1 DF', 136.756624871083);
Insert into ERROR
(serial, NAME)
Values
('5A 54 45 47 C1 BE E1 DF', 972.007181716457);
Insert into ERROR
(serial, NAME)
Values
('5A 54 45 47 C1 BE E1 DF', 798.876085259666);
Insert into ERROR
(serial, NAME)
Values
('5A 54 45 47 C1 BE E1 DF', 680.165347656055);
Insert into ERROR
(serial, NAME)
Values
('5A 54 45 47 C1 BE E1 DF', 721.529852703133);
Insert into ERROR
(serial, NAME)
Values
('5A 54 45 47 C1 BE E1 DF', 987.771724877543);
Insert into ERROR
(serial, NAME)
Values
('5A 54 45 47 C1 BE E1 DF', 768.54463374024);
Insert into ERROR
(serial, NAME)
Values
('N/D', 798.86997959289);
COMMIT;
i have a view where i convert the column serial, this is the query in the view:
select
CONCAT (
UTL_RAW.CAST_TO_VARCHAR2 (
HEXTORAW (
SUBSTR (REPLACE (serial, ' ', ''), 1, 8))),
SUBSTR (REPLACE (serial, ' ', ''), 9, 17)) a, name b
from error
when i execute it, i got this error:
SQL> r
1 select
2 CONCAT (
3 UTL_RAW.CAST_TO_VARCHAR2 (
4 HEXTORAW (
5 SUBSTR (REPLACE (serial, ' ', ''), 1, 8))),
6 SUBSTR (REPLACE (serial, ' ', ''), 9, 17)) a, name b
7* from error
ERROR:
ORA-01465: invalid hex number
no rows selected
SQL>
in this case the error is report by this row:
in this case I only have to filter the N/D value but there may be other text strings that cause the view to fail again.
my serious question is whether it can be done to locate the string that is giving errors, if it is possible to put a debug mode in the query to easily find the "bad"value.
Another question was how do a function to make that calculation, and if in that function you could capture the error, and get the select to finish.
thank you very much for you patience
|
|
|
|
Re: help with error executing a view [message #668583 is a reply to message #668581] |
Sat, 03 March 2018 15:00 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> col a format a30
SQL> with data as ( select REPLACE(serial, ' ') serial, name from error )
2 select case
3 when regexp_like(serial,'[0123456789ABCDEF]{16}')
4 then UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW(SUBSTR(serial, 1, 8))) || SUBSTR(serial, 9, 17)
5 else '*** Error '''||serial||''''
6 end a,
7 name b
8 from data
9 /
A B
------------------------------ ----------
ZTEGC1BEE1DF 259.38343
ZTEGC1BEE1DF 309.375919
ZTEGC1BEE1DF 562.557831
ZTEGC1BEE1DF 136.756625
ZTEGC1BEE1DF 972.007182
ZTEGC1BEE1DF 798.876085
ZTEGC1BEE1DF 680.165348
ZTEGC1BEE1DF 721.529853
ZTEGC1BEE1DF 987.771725
ZTEGC1BEE1DF 768.544634
*** Error 'N/D' 798.86998
11 rows selected.
[Updated on: Sat, 03 March 2018 15:01] Report message to a moderator
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 15:38:50 CDT 2024
|