CODEWITHSUNDEEP
oracle-databaseregexp-substr
pats4u
pats4u

Reputation: 271

Extract part of the string using REGEXP_SUBSTR

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.

Upvotes: 0

Views: 511

Answers (2)

Michael Piankov
Michael Piankov

Reputation: 1997

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

Upvotes: 1

user5683823
user5683823

Reputation:

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

Upvotes: 0

Related Questions