0
  --Function to match multiple pattern and replace it with  its  particular mask.  

  create or replace function masking(in_event_desc in varchar2)
  return varchar2
  as
  c_pattern other_table.pattern%type;
  c_mask other_table.mask%type;
  out_event_desc varchar2(4000) := in_event_desc; 
--desc is like my  name is XYZ and my phone no is 123456 and CC no is 789456.
  cursor val is  select pattern,mask from other_table;
--selects pattern like [0-9][0-9][0-9][0-9] and mask like #### plus all other various kind of patterns and their mask.
  begin
   open val;
  loop
   fetch val into c_pattern,c_mask;
   exit when val%NOTFOUND;
    out_event_desc := regexp_replace(out_event_desc,c_pattern,c_mask);
  end loop;
  close val;
  return out_event_desc;
  end;

how should I handle the ampersand (&) in a event description
--My name is XYZ & my phone no. is 123456.
--we cannot edit the event description like 'My name is XYZ ' ||'&'||' my phone no. is 123456.'

nadeem
  • 1
  • 4
  • Net sure what you are asking. Your function is using column and variable values, it won't care about ampersands. Where is the problematic event description coming from? Are you asking how to put that value into a table, or how to call your function from SQL\*Plus with that test value as a lteral? The "can't edit" part suggests it will really come from a table, I think, but it isn't clear. Either way, [you might just want this?](http://stackoverflow.com/a/12961305/266304) – Alex Poole Feb 25 '17 at 12:19
  • If you're running an ad hoc query with a string literal you *can* edit the value. But it's a pain, so why not use `set define off` instead, as in the answer I linked to? – Alex Poole Feb 25 '17 at 12:35
  • @AlexPoole take this out_event_desc := regexp_replace(out_event_desc,c_pattern,c_mask); as "select regexp_replace('my name is xyz & my no is 1234','[0-9][0-9][0-9][0-9]','####')as out_event_desc from dual;" it will not run where in_event_desc is a column with description like this – nadeem Feb 25 '17 at 12:37
  • @alex but the query is used inside a function and the function is called inside a procedure then what to do – nadeem Feb 25 '17 at 12:39
  • 2
    The ampersand isn't interpreted by the function or procedure. PL/SQL doesn't know about substitution variables. Your **client** is seeing the ampersand in your script and treating it as a substitution variable, before it compiles the procuedure that has the string literal, and before you call it. Do `set verify on` and see what is actually being executed. Then `set define off` and try again. – Alex Poole Feb 25 '17 at 12:49

0 Answers0