3 Replies Latest reply: Sep 30, 2013 5:42 AM by Purvesh K RSS

    My regexp_replace not working correctly.?

    Erhan_toronto

      I dont want to see "USER". and     ALTER TRIGGER "USER"."EMP" ENABLE  line from my result when I run my query . how can I get rid off them?

       

       

      OracleCommand Command = new OracleCommand(@"SELECT regexp_replace(dbms_metadata.get_ddl('TRIGGER','" + triggernames + "'),'(CREATE OR REPLACE TRIGGER )("[A-Z]+"\.)(.+)(ALTER TRIGGER .+)','\1\3', 1, 0, 'n')FROM dual", connection))


      query result:


      CREATE OR REPLACE TRIGGER "USER"."EMP"
        BEFORE INSERT OR UPDATE
        of salary
        on employee
        
      for each row
        declare
        v_error VARCHAR2
      (20);
        
      begin
        
      if :new.salary > 10
        
      then
        v_error
      :=:old.first_name||' cannot have that much!';
        raise_application_error
      (-20999,v_error);
        
      end if;
      end;
      ALTER TRIGGER
      "USER"."EMP" ENABLE




      expected result:


      CREATE OR REPLACE TRIGGER "EMP"
        BEFORE INSERT OR UPDATE
        of salary
        on employee
        
      for each row
        declare
        v_error VARCHAR2
      (20);
        
      begin
        
      if :new.salary > 10
        
      then
        v_error
      :=:old.first_name||' cannot have that much!';
        raise_application_error
      (-20999,v_error);
        
      end if;
      end;

        • 1. Re: My regexp_replace not working correctly.?
          Purvesh K


          This way:

           

          with data as

          (

          select 'CREATE OR REPLACE TRIGGER "USER"."EMP"

            BEFORE INSERT OR UPDATE

            of salary

            on employee

             for each row

            declare

            v_error VARCHAR2(20);

             begin

             if :new.salary > 10

             then

            v_error:=:old.first_name||'' cannot have that much!'';

            raise_application_error(-20999,v_error);

             end if;

          end;

          ALTER TRIGGER "USER"."EMP" ENABLE

          ' col from dual

          )

          select regexp_replace (col, '(".+?")\.(".+?")', '\2')

            from data;

           

          REGEXP_REPLACE(COL,'(".+?")\.(".+?")','\2')

          --------------------------------------------------------

          CREATE OR REPLACE TRIGGER "EMP"

          BEFORE INSERT OR UPDATE

          of salary

          on employee

          for each row

          declare

          v_error VARCHAR2(20);

          begin

          if :new.salary > 10

          then

          v_error:=:old.first_name||' cannot have that much!';

          raise_application_error(-20999,v_error);

          end if;

          end;

          ALTER TRIGGER "EMP" ENABLE

          • 2. Re: My regexp_replace not working correctly.?
            Erhan_toronto

            thanks for reply but I can still see ALTER TRIGGER "EMP" ENABLE and can you correct my regex because I want to use this regex to multiple triggers not only this one

            • 3. Re: My regexp_replace not working correctly.?
              Purvesh K

              Perhaps this:

               

              with data as

              (

              select 'CREATE OR REPLACE TRIGGER "USER"."EMP"

                BEFORE INSERT OR UPDATE

                of salary

                on employee

                 for each row

                declare

                v_error VARCHAR2(20);

                 begin

                 if :new.salary > 10

                 then

                v_error:=:old.first_name||'' cannot have that much!'';

                raise_application_error(-20999,v_error);

                 end if;

              end;

              ALTER TRIGGER "USER"."EMP" ENABLE

              ' col from dual

              )

              select regexp_replace( regexp_replace (col, '(".+?")\."(.+?)"', '\2'), '(.*?)(ALTER TRIGGER .* ENABLE)', '\1') replaced_trigger

                from data;

               

              REPLACED_TRIGGER

              ------------------------------------------------------

              CREATE OR REPLACE TRIGGER EMP

              BEFORE INSERT OR UPDATE

              of salary

              on employee

              for each row

              declare

              v_error VARCHAR2(20);

              begin

              if :new.salary > 10

              then

              v_error:=:old.first_name||' cannot have that much!';

              raise_application_error(-20999,v_error);

              end if;

              end;

               

              This regexp should cater any trigger code of similar format. If it does not, then please post the trigger code so that the reg exp can be modified to cater it.