Oracle: REGEXP_REPLACE

The result I got from a table column has Oracle error information, for example:

select replace(err_msg, chr(10),' ') || '|'
                  from job_status
                  where job_trans_id=123456;

ORA-04045: errors during recompilation/revalidation of ORA-04052: error occurred when looking up remote object ORA-00604: error occurred at recursive SQL level 2 ORA-01017: invalid username/password; logon denied ORA-02063: preceding line from DBLINK_ABC.WORLD|

I'd like to search the Oracle error code and to add <b> & </b> in between the Oracle error code, something like this:

<b>ORA-04045:</b> errors during recompilation/revalidation of <b>ORA-04052:</b> error occurred when looking up remote object <b>ORA-00604:</b> error occurred at recursive SQL level 2 <b>ORA-01017:</b> invalid username/password; logon denied <b>ORA-02063:</b> preceding line from DBLINK_ABC.WORLD|

I use REGEXP_REPLACE function as followed, but it does not work.  What did I do run for this query?

select REGEXP_REPLACE('(replace(err_msg,chr(10),' ') || '|')','([[:ORA:]])\-([[:digit:]]{5})','<b>\1 \2</b>') from job_status where job_trans_id=123456;

thanks.
scpigAsked:
Who is Participating?
 
JPrzybyszewskiConnect With a Mentor Commented:
Hi

You should use the following code:

SQL> select regexp_replace(err_msg,'(ORA-[[:digit:]]{5})','<b>\1</b>') from error;

REGEXP_REPLACE(ERR_MSG,'(ORA-[[:DIGIT:]]{5})','<B>\1</B>')
--------------------------------------------------------------------------------
<b>ORA-04045</b>: errors during recompilation/revalidation of <b>ORA-04052</b>:
error occurred when looking up remote object <b>ORA-00604</b>: error occurred at
 recursive SQL level 2 <b>ORA-01017</b>: invalid username/password; logon denied
 <b>ORA-02063</b>: preceding line from DBLINK_ABC.WORLD|

Best regards,
Jarek
0
 
Javier MoralesOracle DBACommented:
The issue you're facing is that "JOB_STATUS" is a synonym for a remote object, accessed through a dblink that connects your database to another database.

Dblinks have connection string, username and password, and the login attributes of the dblink are wrong (maybe obsolete, maybe password was changed,...)

ORA-01017: invalid username/password; logon denied

This is the main issue. Look for the synonym, get the dblink properties (dba_db_links view) and recreate the dblink using the right username/password.

Cheers,
Javier
0
 
scpigAuthor Commented:
My question is not a dblink issue.   I'd like to know how to use REGEXP_REPLACE function correctly for my query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.