Oracle multiple character substitution implementations

CREATE TABLE A_test
(
Payout_item_code VARCHAR2 () NOT NULL,
Formula_det VARCHAR2 (1000)
)
CREATE table

Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30228 ', ' ({30015}+{30016}) *450 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30102 ', ' ({30015}+{30016}) *1500 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30102 ', ' ({30015}+{30016}) *5000 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30102 ', ' ({30015}+{30016}) *2500 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30102 ', ' ({30015}+{30016}) *2300 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30102 ', ' ({30015}+{30016}) *1150 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30104 ', ' ({30015}+{30016}) *300*12 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30226 ', ' {30057}*2300 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30102 ', ' ({30015}+{30016}) *5000 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30102 ', ' ({30015}+{30016}) *3000 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30102 ', ' ({30015}+{30016}) *1500 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30226 ', ' {30006}+{30061}+{30008} ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30226 ', ' {30057}*3800*12 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30210 ', ' ({30030}+{30031}+{30032}) *38000+{30033}*23000 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30210 ', ' ({30030}+{30031}+{30032}+{30033}) *38000+{30036}*10000 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30229 ', ' ({30015}+{30016}) *1400 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30228 ', ' ({30015}+{30016}) *450 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30216 ', ' ({30015}+{30016}) *1300 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30216 ', ' ({30015}+{30016}) *650 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30307 ', ' ({30015}+{30016}) *360 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30302 ', ' {30051} ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30302 ', ' {30052} ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30302 ', ' {30053} ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30302 ', ' {30054} ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30302 ', ' {30055} ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30302 ', ' {30056} ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30226 ', ' {30057}*4000 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30226 ', ' {30057}*3800 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30226 ', ' {30057}*100*12 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30226 ', ' {30057}*500*12 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30226 ', ' {30060}*0 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30226 ', ' {30057}/{30057}*150000 ');
Insert into A_test (Payout_item_code, Formula_det)
VALUES (' 30226 ', ' {30057}*6000 ');

Select GID, Payout_item_code, Formula_det, Max (substr (TXT, 1, Length (TXT)-1)) from (
Select A.gid,
A.payout_item_code,
A.formula_det,
Replace (Sys_connect_by_path decode (b.element_id, NULL, a.signal, replace (signal, b.element_id, b.name)), ' # # ', ' # # ', ") txt
From
(select GID, Payout_item_code, Formula_det, Row_number () over (partition by GID Order by level) RN,
SUBSTR (Formula_det, Decode (rownum-(allcnt-selfcnt), 1, 1, InStr (Formula_det, '} ', 1, rownum-(allcnt-selfcnt)-1) +1), InStr (Formula_det, '} ', 1, rownum-(allcnt-selfcnt))-decode (rownum-(allcnt-selfcnt), 1, 0, InStr (Formula_det, '} ', 1, rownum-(allcnt-selfcnt)-1)) signal
From (select A.payout_item_code, A.rowid GID,
a.formula_det| | '} ' Formula_det,
Length (A.formula_det)-
Length (replace (A.formula_det, '} ', ')) + 1 selfcnt,
Sum (Length (a.formula_det)-Length (replace (A.formula_det, '} ', ')) (+1) over (order by ROWID) allcnt, sum (Length ( A.formula_det)-Length (replace (A.formula_det, '} ', ')) (+1) over () sumcnt
From A_test a) T1
Start with (allcnt-selfcnt) =0 Connect by RowNum < sumcnt+1 and InStr (Formula_det, '} ', 1, rownum-(allcnt-selfcnt)) > 0) A
Left join B_test B on InStr (a.signal| | '} ', ' {' | | b.element_id| | '} ', 1, 1 >0
Start with A.RN = 1 Connect by prior A.gid = A.gid and prior a.rn + 1 = a.rn)
GROUP by GID, Payout_item_code, Formula_det