Skip to Main Content

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;

Comments
Post Details
Added on Sep 30 2013
3 comments
277 views