regular expression to get ORA- errors out of text

Hi All,

what regular expression would you suggest to get all ORA- errors (with from
1 to 5 numbers)
out of some text like this:

'    ORA-00001 ora-0002     ora   ora-003    aasdlkja alksdj alksdj laskdj
ORA-04 aaaa a  a a aora-5   '


I am trying to figure out how to use
regexp_replace to replace all characters around ora-... errors to get
output:

'ORA-00001 ora-0002 ora-003 ORA-04 ora-5'

but can't find appropriate expression so far:

with s1 as (select '    ORA-00001 ora-0002     ora   ora-003    aasdlkja
alksdj alksdj laskdj  ORA-04 aaaa a  a a aora-5   ' s from dual)
select regexp_replace(s, *'...'*, '...', 1, 0, 'i') from s1
/


-- 
Regards,
Andrey Goryunov

Other related posts: