asked on

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 & in between the Oracle error code, something like this:

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 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})','\1 \2') from job_status where job_trans_id=123456;

thanks.

Javier Morales

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

scpig

ASKER

My question is not a dblink issue. I'd like to know how to use REGEXP_REPLACE function correctly for my query.

ASKER CERTIFIED SOLUTION

JPrzybyszewski

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial