Oracle declare select into update statement does not work

Question

I had an issue where due to database charset special characters would get weird codes assigned to them, then by getting select ascii(substr(declinereasondesc, 30,1)) from DECLINEREASON t where declinereasonid = 7; I got code (49827) for £ in db charset. I then tried to update the records in database.

Problem that I am getting is that data does not get saved to DB or selecting into value to varchar2(6); somehow changes it and it does not match REGEXP_REPLACE any-more.

It did error when I tried using varchar2(1) which should value, which could be a hint.

declare c varchar2(6);
begin 
select ascii(substr(declinereasondesc, 30,1)) into c from DECLINEREASON t
where declinereasonid = 7;
begin 
  update DECLINEREASON set declinereasondesc = REGEXP_REPLACE(declinereasondesc, '(.+)('||c||')(\d+)', '\1\3 (GBP)');
  commit;
  end;
end;
/
commit;

Update: tried declare c number; no errors but didn't update values ether

Answer 1

This one was caused by me being stupid - forgot to wrap c in chr(c).

declare c number;
begin 
select ascii(substr(declinereasondesc, 30,1)) into c from DECLINEREASON t
where declinereasonid = 7;
begin 
  update DECLINEREASON set declinereasondesc = REGEXP_REPLACE(declinereasondesc, '(.+)('||chr(c)||')(\d+)', '\1\3 (GBP)');
  commit;
  end;
end;
/
  commit;