SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

My regexp_replace not working correctly.?

Erhan_torontoSep 30 2013 — edited Sep 30 2013

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;

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

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

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.

1 - 3

Locked Post

New comments cannot be posted to this locked post.

Locked on Oct 28 2013

Added on Sep 30 2013

#dynamic, #index, #insert, #pivot, #pl, #query, #report, #sqlplus

3 comments

416 views

SQL & PL/SQL

My regexp_replace not working correctly.?

Comments

Post Details