Forum Stats

  • 3,852,669 Users
  • 2,264,127 Discussions
  • 7,905,115 Comments

Discussions

My regexp_replace not working correctly.?

Erhan_toronto
Erhan_toronto Member Posts: 241
edited Sep 30, 2013 1:36AM in SQL & PL/SQL

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?

<span class="typ" style="color: #2b91af;">OracleCommand</span><span class="pln"> </span><span class="typ" style="color: #2b91af;">Command</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="kwd" style="color: #00008b;">new</span><span class="pln"> </span><span class="typ" style="color: #2b91af;">OracleCommand</span><span class="pun">(@</span><span class="str" style="color: #800000;">"SELECT regexp_replace(dbms_metadata.get_ddl('TRIGGER','"</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> triggernames </span><span class="pun">+</span><span class="pln"> </span><span class="str" style="color: #800000;">"'),'(CREATE OR REPLACE TRIGGER )("</span><span class="pun">[</span><span class="pln">A</span><span class="pun">-</span><span class="pln">Z</span><span class="pun">]+</span><span class="str" style="color: #800000;">"\.)(.+)(ALTER TRIGGER .+)','\1\3', 1, 0, 'n')FROM dual"</span><span class="pun">,</span><span class="pln"> connection</span><span class="pun">))</span>


query result:


<span class="pln">CREATE OR REPLACE TRIGGER </span><span class="str" style="color: #800000;">"USER"</span><span class="pun">.</span><span class="str" style="color: #800000;">"EMP"</span><span class="pln"><br/>  BEFORE INSERT OR UPDATE<br/>  of salary<br/>  on employee<br/>   </span><span class="kwd" style="color: #00008b;">for</span><span class="pln"> each row<br/>  declare<br/>  v_error VARCHAR2</span><span class="pun">(</span><span class="lit" style="color: #800000;">20</span><span class="pun">);</span><span class="pln"><br/>   </span><span class="kwd" style="color: #00008b;">begin</span><span class="pln"><br/>   </span><span class="kwd" style="color: #00008b;">if</span><span class="pln"> </span><span class="pun">:</span><span class="kwd" style="color: #00008b;">new</span><span class="pun">.</span><span class="pln">salary </span><span class="pun">></span><span class="pln"> </span><span class="lit" style="color: #800000;">10</span><span class="pln"><br/>   </span><span class="kwd" style="color: #00008b;">then</span><span class="pln"><br/>  v_error</span><span class="pun">:=:</span><span class="pln">old</span><span class="pun">.</span><span class="pln">first_name</span><span class="pun">||</span><span class="str" style="color: #800000;">' cannot have that much!'</span><span class="pun">;</span><span class="pln"><br/>  raise_application_error</span><span class="pun">(-</span><span class="lit" style="color: #800000;">20999</span><span class="pun">,</span><span class="pln">v_error</span><span class="pun">);</span><span class="pln"><br/>   </span><span class="kwd" style="color: #00008b;">end</span><span class="pln"> </span><span class="kwd" style="color: #00008b;">if</span><span class="pun">;</span><span class="pln"><br/></span><span class="kwd" style="color: #00008b;">end</span><span class="pun">;</span><span class="pln"><br/>ALTER TRIGGER </span><span class="str" style="color: #800000;">"USER"</span><span class="pun">.</span><span class="str" style="color: #800000;">"EMP"</span><span class="pln"> ENABLE</span>




expected result:


<span class="pln">CREATE OR REPLACE TRIGGER </span><span class="str" style="color: #800000;">"EMP"</span><span class="pln"><br/>  BEFORE INSERT OR UPDATE<br/>  of salary<br/>  on employee<br/>   </span><span class="kwd" style="color: #00008b;">for</span><span class="pln"> each row<br/>  declare<br/>  v_error VARCHAR2</span><span class="pun">(</span><span class="lit" style="color: #800000;">20</span><span class="pun">);</span><span class="pln"><br/>   </span><span class="kwd" style="color: #00008b;">begin</span><span class="pln"><br/>   </span><span class="kwd" style="color: #00008b;">if</span><span class="pln"> </span><span class="pun">:</span><span class="kwd" style="color: #00008b;">new</span><span class="pun">.</span><span class="pln">salary </span><span class="pun">></span><span class="pln"> </span><span class="lit" style="color: #800000;">10</span><span class="pln"><br/>   </span><span class="kwd" style="color: #00008b;">then</span><span class="pln"><br/>  v_error</span><span class="pun">:=:</span><span class="pln">old</span><span class="pun">.</span><span class="pln">first_name</span><span class="pun">||</span><span class="str" style="color: #800000;">' cannot have that much!'</span><span class="pun">;</span><span class="pln"><br/>  raise_application_error</span><span class="pun">(-</span><span class="lit" style="color: #800000;">20999</span><span class="pun">,</span><span class="pln">v_error</span><span class="pun">);</span><span class="pln"><br/>   </span><span class="kwd" style="color: #00008b;">end</span><span class="pln"> </span><span class="kwd" style="color: #00008b;">if</span><span class="pun">;</span><span class="pln"><br/></span><span class="kwd" style="color: #00008b;">end</span><span class="pun">;</span>

Answers

  • Purvesh K
    Purvesh K Member Posts: 2,319 Silver Trophy


    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
    
  • 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

  • Purvesh K
    Purvesh K Member Posts: 2,319 Silver Trophy
    edited Sep 30, 2013 1:42AM

    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.

This discussion has been closed.