Extract part of the string using REGEXP_SUBSTR

Question

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.

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? — user5683823
– user5683823, Commented Oct 25, 2016 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
– pats4u, Commented Oct 26, 2016 at 4:49

Michael Piankov · Accepted Answer · 2016-10-26 08:22:06Z

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(.+)(.+.\"(.+)\")'

"^" - begin of the string
"ORA-" - simple string "ORA-"
"\d+" - series of digits
":" - simple colon
"\s" - white-space
"(.+)" - the longest string of any characters. And () mean that is will be captured as group(\1).
"(" - left bracket
".+" - the longest string of any characters.
"."- simple dot
\" - double quote
(.+) - the longest string of any characters. And next group (\2)
\" - double quote
)' - 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

edited Oct 26, 2016 at 8:22

answered Oct 25, 2016 at 13:08

Michael Piankov

1,9971 gold badge11 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

pats4u Over a year ago

Thanks Michael. Would really appreciate if you could explain a little bit how this actually works .

pats4u Over a year ago

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.

Michael Piankov Over a year ago

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

pats4u Over a year ago

Wow ! that was quick & perfect . Thanks

pats4u Over a year ago

Hi Michael, Can you please help with regexp_replace to translate ORA-00001: unique constraint (SCOTT.SYS_C0010807) violated to Duplicates in the file.

user5683823 · Accepted Answer · 2016-10-25 13:16:47Z

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

answered Oct 25, 2016 at 13:16

user5683823

Collectives™ on Stack Overflow

Extract part of the string using REGEXP_SUBSTR

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related