OEM extract ORA- from string text 1

[mibeach7]

I would like to extract all "ORA-" errors from OEM then group by and count by error code.

Here is the text:
SQL> select SUMMARY_MSG from sysman.MGMT$INCIDENTS where SUMMARY_MSG like '%ORA-%';
Failed to connect: java.sql.SQLException: Listener refused the connection with
the following error: ORA-12505, TNS:listener does not currently know of SID give
n in connect descriptor.

So I just want the error codes from the string above "ORA-12505" output.

I tried this but it gives me text after the ORA-12505 that I do not want, think I need to trim function?
SQL> SELECT SUBSTR(msg, INSTR(msg,'ORA-', -4) +1) FROM sysman.mgmt$events where MSG like '%ORA-%' and rownum < 5;
RA-12505, TNS:listener does not currently know of SID given in connect descripto

Thanks for any tips!

 

[Andrzejek]

Looks like you need to add [blue]how long of a string[/blue] you want to get back, which is first (the only?) occurrence of a comma:
[tt]
SUBSTR(msg, INSTR(msg, 'ORA-', -4)[blue], INSTR(msg, ’,')[/blue]) As MyField[/tt]


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mibeach7]

Yes this worked!
SQL> select SUBSTR(msg, INSTR(msg, 'ORA-', -4),9) as ERR FROM sysman.mgmt$events where MSG like '%ORA-%' and rownum < 6;

ERR
------------------------------------
ORA-12505
ORA-12505
ORA-12505
ORA-28000
ORA-28000