Link to home
Create AccountLog in
Avatar of Sarma Vadlamani
Sarma Vadlamani

asked on

oracle query

hi i want to get
the data only from second error i.e., using regexp or substr
ORA-28007:The pwd cannot be reused.
ORA-06502: PL/SQL:numeric or value error ORA-28007:The pwd cannot be reused.

thanks.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Assuming that you have the error message in column COL1, this should do it:

SUBSTR(COL1, INSTR(COL1, 'ORA-', 1, 2))
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Just in case you ever want just the second error no matter what:
with mydata as(
select 'ORA-28007:The pwd cannot be reused. 
 ORA-06502: PL/SQL:numeric or value error ORA-28007:The pwd cannot be reused.
' myerror from dual
)
select
regexp_substr(myerror,'(ORA-[0-9]{5}.+)(ORA-[0-9]{5})',1,1,null,1)
from mydata;

Open in new window


This returned:
ORA-06502: PL/SQL:numeric or value error