Replace some variables by data of another table in sql oracle

I have a table with two columns

in a translation table I have the signification of the variables %X that looks like

I want to replace in my first table all the variables by the description, so the result has to looks like this:

I tried with a replace, but I didn't figured out how to make it work

To replace all variables you could use a recursive algorithm:

with data(typ, txt) as (
    select 'A', 'this is some text for %1 and %2' from dual union all
    select 'B',	'this is another step for %1' from dual 
),
translations(typ, var, description) as (
   select 'A', '%1', '#person1#' from dual union all
   select 'A', '%2', '#person2#' from dual union all
   select 'B', '%1', '#manager#' from dual -- union all
),
rtranslations(typ, var, description,rn) as (
    select t.*, row_number() over(partition by typ order by var) as rn
        from translations t
),
replacecte(typ, txt, replaced_txt, rn) as (
    select d.typ, d.txt, replace(d.txt, t.var, t.description), t.rn
    from data d
    join rtranslations t on t.typ = d.typ
    where t.rn = 1

    union all

    select r.typ, r.txt, replace(r.replaced_txt, t.var, t.description), t.rn
    from replacecte r
    join rtranslations t on t.typ = r.typ and t.rn = r.rn + 1
)
select r.typ, r.txt, replaced_txt from replacecte r
where rn = length(txt) - length(replace(txt,'%',''))
;

You can also do it this way without recursion. data and descr are of course just mock ups for your tables, you would not need any WITH clauses. This method uses the steps (1) break up the sentences into words, (2) outer join using those words to your description table, replacing any matches with the description values, (3) reassemble the words back into sentences using LISTAGG.

WITH data AS(SELECT 'A' type, 'this is some text for %1 and %2' txt FROM dual
              UNION ALL
             SELECT 'B' type, 'this is another step for %1' txt FROM dual
             ),
     descr AS (SELECT 'A' type, '%1' variable,'#person1#' description FROM dual
               UNION ALL
               SELECT 'A' type, '%2' variable,'#person2#' description FROM dual
               UNION ALL
               SELECT 'B' type, '%1' variable,'#manager#' description FROM dual)
SELECT type,
       LISTAGG(new_word,' ') WITHIN GROUP (ORDER BY seq) txt
  FROM (SELECT x.type,
               NVL(descr.description,x.word) new_word,
               seq
          FROM (SELECT type,SUBSTR(' '||txt,INSTR(' '||txt,' ',1,seq)+1,INSTR(' '||txt||' ',' ',1,seq+1) - (INSTR(' '||txt,' ',1,seq)+1)) word,seq
                  FROM data,
                       (SELECT ROWNUM seq FROM dual CONNECT BY LEVEL <= 50) x) x,
               descr
         WHERE x.type = descr.type(+)
           AND x.word = descr.variable(+))
 GROUP BY type

You could use PIVOT to get the var values from rows into columns (geting all vars in the same row with text) and then do multiple replaces depending on number of var values:

SELECT      t.A_TYPE, 
            CASE  WHEN d.V3 Is Not Null  THEN REPLACE(REPLACE(REPLACE(t.TXT, '%1', d.V1), '%2', d.V2), '%3', d.V3)
                  WHEN d.V2 Is Not Null  THEN REPLACE(REPLACE(t.TXT, '%1', d.V1), '%2', d.V2)
                  WHEN d.V1 Is Not Null  THEN REPLACE(t.TXT, '%1', d.V1)
            ELSE  t.TXT
            END "TXT"
FROM        tbl t
INNER JOIN  ( SELECT  *
              FROM    ( Select A_TYPE, VAR, DESCRIPTION FROM descr  )
                      PIVOT ( MAX(DESCRIPTION) For VAR IN('%1' "V1", '%2' "V2", '%' "V3") )
            ) d ON(d.A_TYPE = t.A_TYPE)

With sample data as:

WITH 
    tbl (A_TYPE, TXT) AS
        (
            Select 'A', 'this is some text for %1 and %2'  From Dual Union All
            Select 'B', 'this is another step for %1'      From dual
        ),
     descr (A_TYPE, VAR, DESCRIPTION) AS 
        (
            Select 'A', '%1', '#person1#'  From Dual UNION ALL
            Select 'A', '%2', '#person2#'  From Dual UNION ALL
            Select 'B', '%1', '#manager#'  From Dual
        )

... the result should be

A_TYPE TXT                                            
------ -----------------------------------------------
A      this is some text for #person1# and #person2#  
B      this is another step for #manager#