Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table1 (ID, TEXT) AS
SELECT 1, 'It is %1 today with a %2 sky.' FROM DUAL UNION ALL
SELECT 2, 'It is %1 today with %2 and chances of %3.' FROM DUAL UNION ALL
SELECT 3, 'I just %1 have a %2 that %3 understand. We %4 need %5 to %6 on.' FROM DUAL UNION ALL
SELECT 4, '%1%2%3%4%5%6%7%8%9%10' FROM DUAL;
4 rows affected

CREATE TABLE Table2 (ID, VALS) AS
SELECT 1, 'sunny/bright blue' FROM DUAL UNION ALL
SELECT 2, 'rainy/severe thunder storms/flooding' FROM DUAL UNION ALL
SELECT 3, 'might/problem/you''ll/all/somebody/lean' FROM DUAL UNION ALL
SELECT 1, 'rainy/gloomy' FROM DUAL UNION ALL
SELECT 4, 'A/B/C/D/E/F/G/H/I/J' FROM DUAL;
5 rows affected

WITH replacements (id, text, variables, idx) AS (
  SELECT t1.id,
         t1.text,
         t2.vals,
         LENGTH(t2.vals) - LENGTH(REPLACE(t2.vals, '/')) + 1
  FROM   table1 t1
         INNER JOIN table2 t2
         ON (t1.id = t2.id)
UNION ALL
  SELECT id,
         REPLACE(
           text,
           '%'||idx,
           SUBSTR(variables, INSTR(variables, '/', -1) + 1)
         ),
         SUBSTR(variables, 1, INSTR(variables, '/', -1) - 1),
         idx - 1
  FROM   replacements
  WHERE  idx > 0
)
SELECT id, text
FROM   replacements
WHERE  idx = 0;


ID
TEXT


1
It is sunny today with a bright blue sky.

1
It is rainy today with a gloomy sky.

2
It is rainy today with severe thunder storms and chances of flooding.

3
I just might have a problem that you'll understand. We all need somebody to lean on.

4
ABCDEFGHIJ

ID	TEXT
1	It is sunny today with a bright blue sky.
1	It is rainy today with a gloomy sky.
2	It is rainy today with severe thunder storms and chances of flooding.
3	I just might have a problem that you'll understand. We all need somebody to lean on.
4	ABCDEFGHIJ