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 Go to next message
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 #668574 is a reply to message #668572] Sat, 03 March 2018 07:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

We don't have your tables.
We don't have your data.
Therefore we can't write SQL to solve your problem.
Does table have Primary Key?

http://www.orafaq.com/forum/mv/msg/204147/666852/#msg_666852
Re: help with error executing a view [message #668575 is a reply to message #668574] Sat, 03 March 2018 07:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
One way to identify the culprit row would be to TRANSLATE 0-9 & A-F to NULL.
This results in all valid rows have LENGTH=0 & rows where LENGTH>0 contain invalid characters
Re: help with error executing a view [message #668576 is a reply to message #668574] Sat, 03 March 2018 08:16 Go to previous messageGo to next message
laredoeneko
Messages: 21
Registered: November 2017
Junior Member
Thank you very much, at the end I have done what you indicated to me, I have used the lenght function to find the rare values...... again thanks........
the question was rather whether you could configure sqlplus in trace mode or something similar so that it would give some information about which row the problem was .. that or, use some function to capture the error in the field, so that converting the substr instead of operations on a column into a function, to capture the sqlerror.... I don't know if I explain myself.
COlumn donot have a primeary key
regards
Cesar

Re: help with error executing a view [message #668577 is a reply to message #668576] Sat, 03 March 2018 08:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
laredoeneko wrote on Sat, 03 March 2018 06:16
Thank you very much, at the end I have done what you indicated to me, I have used the lenght function to find the rare values...... again thanks........
the question was rather whether you could configure sqlplus in trace mode or something similar so that it would give some information about which row the problem was .. that or, use some function to capture the error in the field, so that converting the substr instead of operations on a column into a function, to capture the sqlerror.... I don't know if I explain myself.
COlumn donot have a primeary key
regards
Cesar

Solution depends upon what is acceptable or desired results which you have NOT clearly stated.
Is goal to successfully process valid data?
Is goal to identify the rows with bad data?
A well defined FUNCTION is implemented to only do 1 thing & NOT both above.
What problem are you really trying to solve?
Re: help with error executing a view [message #668578 is a reply to message #668577] Sat, 03 March 2018 11:59 Go to previous messageGo to next message
laredoeneko
Messages: 21
Registered: November 2017
Junior Member
sorry for my bad explanation.....
actually the query fails, so it will be acceptable that the view check the valid data......if the format is not valid, not applay
secondly, I asked how to troubleshoot problems in this kind of situations, if it was possible for sqlplus to give some more kind of information about the field that had failed.... just to expand my knowledge on the subject.
if the input format is invalid, in this case a hexadecimal string, if possible check the field and if it matches the format, apply the formula.?
best regard
Cesar

Re: help with error executing a view [message #668579 is a reply to message #668576] Sat, 03 March 2018 12:08 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Could you use DML error logging,
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/managing-tables.html#GUID-B71385AD-2D21-42F3-9249-3D6364887746
you could run some sort of dummy update statement against the table that would write out the details of any problem rows to the error logging table.

TO_DATE now has an ON CONVERSION ERROR clause, I hope they add something similar to many other functions.
Re: help with error executing a view [message #668580 is a reply to message #668578] Sat, 03 March 2018 12:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
laredoeneko wrote on Sat, 03 March 2018 09:59
sorry for my bad explanation.....
actually the query fails, so it will be acceptable that the view check the valid data......if the format is not valid, not applay
secondly, I asked how to troubleshoot problems in this kind of situations, if it was possible for sqlplus to give some more kind of information about the field that had failed.... just to expand my knowledge on the subject.
if the input format is invalid, in this case a hexadecimal string, if possible check the field and if it matches the format, apply the formula.?
best regard
Cesar

We are NOT mind readers.
What query? No query was posted!
What view? No view was posted.
Tables do NOT have fields; only columns.
Only 1 column exists is code fragment you posted & it is/was "ont_serial_number".
Do you want to know which column or which row threw the error?
What is the goal?
Do you want to process only good rows?
Do you want to identify bad rows?

PLEASE read & FOLLOW Posting guidelines.
Post CREATE TABLE statements.
Post INSERT statements.
Post desired & expect results based upon provided sample data.
What exactly does successful completion look like?
Re: help with error executing a view [message #668581 is a reply to message #668580] Sat, 03 March 2018 14:35 Go to previous messageGo to next message
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:
N/D	798.86997959289

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 #668582 is a reply to message #668579] Sat, 03 March 2018 14:37 Go to previous messageGo to next message
laredoeneko
Messages: 21
Registered: November 2017
Junior Member
thank you, i try it.
Re: help with error executing a view [message #668583 is a reply to message #668581] Sat, 03 March 2018 15:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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

Re: help with error executing a view [message #668585 is a reply to message #668583] Sat, 03 March 2018 17:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It is Worst Practice to use KEYWORD for table or column name.
  1* select keyword from v$reserved_words where keyword like '%NAME%'
SQL> /

KEYWORD
--------------------------------------------------------------------------------
FILE_NAME_CONVERT
BFILENAME
XMLNAMESPACES
NAME
ORA_DM_PARTITION_NAME
SERVICE_NAME_CONVERT
CON_NAME_TO_ID
GLOBAL_NAME
SOURCE_FILE_NAME_CONVERT
DB_UNIQUE_NAME
HIER_MEMBER_UNIQUE_NAME

KEYWORD
--------------------------------------------------------------------------------
NLS_COLLATION_NAME
SYS_XQFNLNAME
NAMESPACE
RENAME
SYS_PATHID_LASTNAME
HIER_MEMBER_NAME
SYS_XQRENAME
NLS_CHARSET_NAME
NAMED
USE_WEAK_NAME_RESL
EVALNAME

KEYWORD
--------------------------------------------------------------------------------
SESSIONTZNAME
HIER_PARENT_UNIQUE_NAME
QB_NAME
SYS_XQNODENAME

26 rows selected.
Re: help with error executing a view [message #668588 is a reply to message #668583] Sun, 04 March 2018 02:36 Go to previous messageGo to next message
laredoeneko
Messages: 21
Registered: November 2017
Junior Member
thank you very much, it is valid for me and work correctely.
Re: help with error executing a view [message #668590 is a reply to message #668583] Sun, 04 March 2018 06:24 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Either

regexp_like(serial,'^[0123456789ABCDEF]{16}')

or

with data as ( select SUBSTR(REPLACE(serial,' '),1,16) serial, name from error )


SY.
Re: help with error executing a view [message #668591 is a reply to message #668590] Sun, 04 March 2018 11:36 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Arghh! I forgot the ^ and $ in my expression, this was my thought but I didn't write it. Smile

Depends on the clear and complete definition "valid data" which is still missing.

Previous Topic: comma separated string to column dynamically
Next Topic: Oracle Partitioned Table get values
Goto Forum:
  


Current Time: Thu Mar 28 14:43:22 CDT 2024