6 Replies Latest reply: Jan 21, 2013 9:15 AM by 980516 RSS

    How to use REGEXP_REPLACE to remove ORACODES

    980516
      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
          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
            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
              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
                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
                  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
                    Hi guys, I appreciate your concern in answering this thread. Now it working pretty well! Thanks again!