Oracle 23c

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';
WITH car_paint_options (car_model, paint_opts) AS (
   SELECT 'Escort', 'red,blue' FROM dual UNION ALL
   SELECT 'Puma',   'black'    FROM dual
)
SELECT row_number() over(order by level) rn, level, ep.car_model,
       regexp_substr(ep.paint_opts, '[^,]+', 1, level) paint_opt
FROM   car_paint_options ep
CONNECT BY regexp_substr (ep.paint_opts, '[^,]+', 1, level) is not null
AND        ep.car_model = prior ep.car_model
ORA-01436: CONNECT BY loop in user data

WITH car_paint_options (car_model, paint_opts) AS (
   SELECT 'Escort', 'red,blue' FROM dual UNION ALL
   SELECT 'Puma',   'black'    FROM dual
)
SELECT row_number() over(order by level) rn, level, ep.car_model,
       regexp_substr(ep.paint_opts, '[^,]+', 1, level) paint_opt
FROM   car_paint_options ep
CONNECT BY regexp_substr (ep.paint_opts, '[^,]+', 1, level) is not null
AND        ep.car_model = prior ep.car_model
AND        PRIOR SYS_GUID() IS NOT NULL


RN
LEVEL
CAR_MODEL
PAINT_OPT


1
1
Escort
red

2
1
Puma
black

3
2
Escort
blue


WITH car_paint_options (car_model, paint_opts) AS (
   SELECT 'Escort', 'red,blue' FROM dual UNION ALL
   SELECT 'Puma',   'black'    FROM dual
)
SELECT row_number() over(order by depth) rn,
       depth,
       ep.car_model,
       regexp_substr(ep.paint_opts, '[^,]+', 1, depth) AS paint_opt
FROM   car_paint_options ep
       CROSS JOIN LATERAL (
         SELECT LEVEL AS depth
         FROM   DUAL
         CONNECT BY LEVEL <= REGEXP_COUNT(ep.paint_opts, '[^,]+')
       )


RN
DEPTH
CAR_MODEL
PAINT_OPT


1
1
Escort
red

2
1
Puma
black

3
2
Escort
blue


WITH car_paint_options (car_model, paint_opts) AS (
   SELECT 'Escort', 'red,blue' FROM dual UNION ALL
   SELECT 'Puma',   'black'    FROM dual
),
bounds (car_model, paint_opts, lvl, spos, epos) AS (
  SELECT car_model,
         paint_opts,
         1,
         1,
         INSTR(paint_opts, ',', 1)
  FROM   car_paint_options
UNION ALL
  SELECT car_model,
         paint_opts,
         lvl + 1,
         epos + 1,
         INSTR(paint_opts, ',', epos + 1)
  FROM   bounds
  WHERE  epos > 0
)
SELECT ROW_NUMBER() OVER (ORDER BY lvl) AS rn,
       lvl,
       car_model,
       CASE epos
       WHEN 0
       THEN SUBSTR(paint_opts, spos)
       ELSE SUBSTR(paint_opts, spos, epos - spos)
       END AS paint_opt
FROM   bounds


RN
LVL
CAR_MODEL
PAINT_OPT


1
1
Escort
red

2
1
Puma
black

3
2
Escort
blue