REPLACE a variable with another variable which is stored in a table/variable

Question

I have a table VARIABLE_REPLACE

DATA1	var
TBR_SP	lc_location

I have a requirement if a string variable has 'TBR_SP' then that string should be replaced with the value of lc_location variable.

declare  

str varchar2(1000):='TBR_SP123'; 

data1 varchar2(1000); 

var varchar2(1000);

 lc_location  varchar2(1000):='LONDON';

 to_be_replaced varchar2(1000); 

 sql1 varchar2(1000); 

 BEGIN  

 SELECT data1, var INTO to_be_replaced, var FROM VARIABLE_REPLACE;

 --to_be_replaced:='TBR_SP';

 --var:='lc_location'; 

 str := REPLACE(str ,to_be_replaced ,var ); 

 dbms_output.put_line(str); 

 END; 

 /

Above block returning:lc_location123 expected is LONDON123.

tried with execute immediately, tried with replacing twice.

Answer 1

How about using an associative array instead of individual variables? That way you can refer to (for example) my_array(var) to retrieve the value associated with whatever is in var.

create table variable_replace (data1, var) as
select 'TBR_SP', 'lc_location' from dual;

declare
    str varchar2(1000) := 'TBR_SP123';
    var varchar2(1000);
    lc_location varchar2(1000) := 'LONDON';
    to_be_replaced varchar2(1000);
    
    type var_list is table of varchar2(200) index by varchar2(100);
    vars var_list;
begin
    vars('lc_location') := 'LONDON';

    select data1, var into to_be_replaced, var
    from   variable_replace;

    str := replace(str, to_be_replaced, vars(var));

    dbms_output.put_line(str);
end;