Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (value) AS
SELECT 'Att1:Val1,[Txt1,Txt2:Txt3]:Val2' FROM DUAL;
1 rows affected

WITH matches (value, lvl, substr1, substr2, epos) AS (
  SELECT value,
         1,
         REGEXP_SUBSTR(value, '(\[.*?\]|.*?):(.*?)(,|$)', 1, 1, NULL, 1),
         REGEXP_SUBSTR(value, '(\[.*?\]|.*?):(.*?)(,|$)', 1, 1, NULL, 2),
         REGEXP_INSTR(value, '(\[.*?\]|.*?):(.*?)(,|$)', 1, 1, 1)
  FROM   table_name
UNION ALL
  SELECT value,
         lvl + 1,
         REGEXP_SUBSTR(value, '(\[.*?\]|.*?):(.*?)(,|$)', epos, 1, NULL, 1),
         REGEXP_SUBSTR(value, '(\[.*?\]|.*?):(.*?)(,|$)', epos, 1, NULL, 2),
         REGEXP_INSTR(value, '(\[.*?\]|.*?):(.*?)(,|$)', epos, 1, 1)
  FROM   matches
  WHERE  epos > 0
)
SELECT lvl,
       substr1,
       substr2
FROM   matches
WHERE  epos > 0;


LVL
SUBSTR1
SUBSTR2


1
Att1
Val1

2
[Txt1,Txt2:Txt3]
Val2


WITH matches (value, lvl, spos, mpos, epos) AS (
  SELECT value,
         1,
         1,
         CASE
         WHEN SUBSTR(value, 1, 1) = '['
         THEN INSTR(value, ']:', 1) + 1
         ELSE INSTR(value, ':', 1)
         END,
         INSTR(
           value,
           ',',
           CASE
           WHEN SUBSTR(value, 1, 1) = '['
           THEN INSTR(value, ']:', 1) + 1
           ELSE INSTR(value, ':', 1)
           END
         )
  FROM   table_name
UNION ALL
  SELECT value,
         lvl + 1,
         epos + 1,
         CASE
         WHEN SUBSTR(value, epos + 1, 1) = '['
         THEN INSTR(value, ']:', epos + 1) + 1
         ELSE INSTR(value, ':', epos + 1)
         END,
         INSTR(
           value,
           ',',
           CASE
           WHEN SUBSTR(value, epos + 1, 1) = '['
           THEN INSTR(value, ']:', epos + 1) + 1
           ELSE INSTR(value, ':', epos + 1)
           END
         )
  FROM   matches
  WHERE  epos > 0
)
SELECT lvl,
       SUBSTR(value, spos, mpos - spos) AS substr1,
       CASE epos
       WHEN 0
       THEN SUBSTR(value, mpos + 1)
       ELSE SUBSTR(value, mpos + 1, epos - mpos - 1)
       END AS substr2
FROM   matches;


LVL
SUBSTR1
SUBSTR2


1
Att1
Val1

2
[Txt1,Txt2:Txt3]
Val2