0

I am using the error log table through dbms_errlog.create_error_log for DML operation error's in bulk & returning the ORA_ERR_MESG$ back to the Client. However i need to ignore the error code , so it looks user friendly.

Ex: ORA-01400: cannot insert NULL into ("ABC_OWNER"."ABC_PART"."REGION")

this needs to be changed to "Cannot insert NULL into REGION"

I have tried REGEXP_SUBSTR to search for "ORA-" pattern & do some extraction but i have succeeded to only certain extent. Can you please advise on how to do this.

Regards.

pats4u
  • 177
  • 6
  • 18
  • What have you tried? Also: you didn't just remove the error code, you also changed the message: you just show what column you can't insert NULL in, not the schema and the table. Will this help you or prove to be a bad idea in the future? –  Oct 25 '16 at 13:07
  • In the actual use case, i do not need to show the schema or table since user is uploading data into the table & already knows about it. user just needs to know if there is any error during upload . In my implementation, i am merging user uploaded data into actual table & logging any error in error log table & returning these error's back if any. I just want to keep it simple & readable without Error Codes , Schema names etc. Thanks – pats4u Oct 26 '16 at 04:49

2 Answers2

1

there is a lot exception n oracle and there are have different masks are you want to code all of them?

You currenct regexp may be looked like:

select regexp_replace('ORA-01400: cannot insert NULL into ("ABC_OWNER"."ABC_PART"."REGION")'
                     ,'^ORA-\d+:\s(.+)\(.+\.\"(.+)\"\)', '\1 \2') from dual

EDIT: description of regexp '^ORA-\d+:\s(.+)(.+.\"(.+)\")'

  1. "^" - begin of the string
  2. "ORA-" - simple string "ORA-"
  3. "\d+" - series of digits
  4. ":" - simple colon
  5. "\s" - white-space
  6. "(.+)" - the longest string of any characters. And () mean that is will be captured as group(\1).
  7. "(" - left bracket
  8. ".+" - the longest string of any characters.
  9. "."- simple dot
  10. \" - double quote
  11. (.+) - the longest string of any characters. And next group (\2)
  12. \" - double quote
  13. )' - right bracket

Regexp in points 6,8 and 11 will be hungry search it will try to fined the longest string from first group. It mean if there are some variants between length of "6","8" and "11". "6" will chose first and take all that it can "8" will chose next and "11" least. For instance if you have string aaaaaa and regexp (.+)(.+) the first group will take aaaaa and the second a

Michael Piankov
  • 1,989
  • 1
  • 8
  • 19
  • Thanks Michael. Would really appreciate if you could explain a little bit how this actually works . – pats4u Oct 26 '16 at 04:46
  • Thanks Michael for the explanation . If the error was ORA-12899: value too large for column "ABC_OWNER"."ABC_PART"."REGION" (actual: 6, maximum: 4) , how can we change this to : "value too large for column REGION". Can both be covered in same regexp statement. – pats4u Oct 26 '16 at 08:54
  • 1
    I think you may try something like `regexp_replace(' ORA-12899: value too large for column "ABC_OWNER"."ABC_PART"."REGION" (actual: 6, maximum: 4)' '^\s*ORA-\d+:\s(.+?)\(?(\".+\"\.)?(\".+\"\.)?\"(.+)\"\)?.*' , '\1\4')` – Michael Piankov Oct 26 '16 at 09:12
  • Wow ! that was quick & perfect . Thanks – pats4u Oct 26 '16 at 10:47
  • Hi Michael, Can you please help with regexp_replace to translate ORA-00001: unique constraint (SCOTT.SYS_C0010807) violated to Duplicates in the file. – pats4u May 15 '17 at 10:44
0

If you just need to remove the error code, that can be done with regular instr and substr, which are faster than regular expression. But it depends on what you really need to do (for example, here I didn't bother capitalizing the first letter since your requirement is likely to be clarified more later anyway).

with
     error_messages ( str ) as (
       select 'ORA-01400: cannot insert NULL into ("ABC_OWNER"."ABC_PART"."REGION")' 
         from dual
     )
select substr( str, instr(str, ' ') + 1) as modified_err_msg from error_messages
;

MODIFIED_ERR_MSG
----------------
cannot insert NULL into ("ABC_OWNER"."ABC_PART"."REGION")