[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle: REGEXP_REPLACE

Posted on 2011-10-17
3
Medium Priority
?
1,136 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:scpig
3 Comments
 
LVL 7

Expert Comment

by:Javier Morales
ID: 36982491
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
 

Author Comment

by:scpig
ID: 36982546
My question is not a dblink issue.   I'd like to know how to use REGEXP_REPLACE function correctly for my query.
0
 
LVL 3

Accepted Solution

by:
JPrzybyszewski earned 1000 total points
ID: 36982578
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question