This discussion is archived
6 Replies Latest reply: Jan 21, 2013 7:15 AM by 980516 RSS

How to use REGEXP_REPLACE to remove ORACODES

980516 Newbie
Currently Being Moderated
Hi guys,

Basically I need to use REGEXP_REPLACE in order to remove from a string certain oracle code pricislly those which are used on RAISE_APPLICATION_ERROR from 20000 to 20999. We have an application here that handles those exceptions and each exception append each other so inside them we can find those ORA-20001: Some error. ]

So I was wondering if I can achieve that by using REGEXP_REPLACE. So which regexp I could use so that I can remove those oracode only!

Thanks!
  • 1. Re: How to use REGEXP_REPLACE to remove ORACODES
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    GuitarBrasil wrote:
    Hi guys,

    Basically I need to use REGEXP_REPLACE in order to remove from a string certain oracle code pricislly those which are used on RAISE_APPLICATION_ERROR from 20000 to 20999. We have an application here that handles those exceptions and each exception append each other so inside them we can find those ORA-20001: Some error. ]

    So I was wondering if I can achieve that by using REGEXP_REPLACE. So which regexp I could use so that I can remove those oracode only!
    Sure. It sounds like you want to use the pattern
    ORA-20\d\d\d
    as in
    SELECT     REGEXP_REPLACE ( err_msg
                     , 'ORA-20\d\d\d'
                            )     AS error_message
    FROM    table_x
    ;
     

    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and also post the results you want from that data.
    If you're asking about a DML statement, such as UPDATE, the sample data will be the contents of the table(s) before the DML, and the results will be state of the changed table(s) when everything is finished.
    Explain, using specific examples, how you get those results from that data.
    Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}

    Edited by: Frank Kulash on Dec 28, 2012 7:02 AM
  • 2. Re: How to use REGEXP_REPLACE to remove ORACODES
    ranit B Expert
    Currently Being Moderated
    select 
      TRIM(
        regexp_replace('ORA-20476 :    divisor is equal to zero',
        'ORA-20(\d){3}(\s)+:','')
      ) 
    from dual;
    
    -- output : "divisor is equal to zero"
    -- OR
    select 
       TRIM(
         translate('ORA-01476: divisor is equal to zero',
         'ORA-0123456789:',' ')
       ) 
    from dual;
    
    -- output : "divisor is equal to zero"
    Edited by: ranit B on Dec 28, 2012 3:12 AM
  • 3. Re: How to use REGEXP_REPLACE to remove ORACODES
    padders Pro
    Currently Being Moderated
    Minor point Frank but your example doesn't appear to match the pattern you posted immediately above.
  • 4. Re: How to use REGEXP_REPLACE to remove ORACODES
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    padders wrote:
    Minor point Frank but your example doesn't appear to match the pattern you posted immediately above.
    Do you mean it doesn't match anything in 'ORA-0123456789:'? That's intentional. The requirment was:
    GuitarBrasil wrote:
    ... I need to use REGEXP_REPLACE in order to remove from a string certain oracle code pricislly those which are used on RAISE_APPLICATION_ERROR from 20000 to 20999.
    I'm only looking for codes where the first 2 digits are 2 and 0, in that order.

    I did have a typo (now corrected) in my original message; I left out the '20' in one place.
  • 5. Re: How to use REGEXP_REPLACE to remove ORACODES
    padders Pro
    Currently Being Moderated
    I did have a typo (now corrected) in my original message
    I was referring (obviously not very clearly) to the typo, which as you say you have now fixed.
  • 6. Re: How to use REGEXP_REPLACE to remove ORACODES
    980516 Newbie
    Currently Being Moderated
    Hi guys, I appreciate your concern in answering this thread. Now it working pretty well! Thanks again!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points