CREATE TABLE die( 
    face_id INT NOT NULL, 
    face_value INT NOT NULL, 
    probability REAL NOT NULL, 
    CONSTRAINT pk_die PRIMARY KEY (face_id));
INSERT INTO die VALUES (1, 1, 1/6 + 1/12); 
INSERT INTO die VALUES (2, 3, 1/6 + 1/12); 
INSERT INTO die VALUES (3, 4, 1/6 + 1/12); 
INSERT INTO die VALUES (4, 5, 1/6 - 1/12); 
INSERT INTO die VALUES (5, 6, 1/6 - 1/12); 
INSERT INTO die VALUES (6, 8, 1/6 - 1/12); 

select * from die order by face_id
/

var N number
exec :N := 2

with number_of_dies as (select count(*) cnt from die)
, all_probabilities as
( select sum_value
       , prob
       , i
    from (select level l from number_of_dies connect by level <= power(cnt,:N))
       , number_of_dies
   model
         reference r on (select face_id, face_value, probability from die)
           dimension by (face_id)
           measures (face_value,probability)
         main m
           partition by (l rn, cnt)
           dimension by (0 i)
           measures (0 die_face_id, 0 sum_value, 1 prob, l remainder)
         rules iterate (1000) until (iteration_number + 1 = :N)
         ( die_face_id[0] = 1 + mod(remainder[0]-1,cv(cnt))
         , remainder[0]   = ceil((remainder[0] - die_face_id[0] + 1) / cv(cnt))
         , sum_value[0]   = sum_value[0] + face_value[die_face_id[0]]
         , prob[0]        = prob[0] * probability[die_face_id[0]]
         )
)
select sum_value "Sum"
     , sum(prob) "Probability"
  from all_probabilities
 group by rollup(sum_value)
 order by sum_value
/

exec :N := 3

with number_of_dies as (select count(*) cnt from die)
, all_probabilities as
( select sum_value
       , prob
       , i
    from (select level l from number_of_dies connect by level <= power(cnt,:N))
       , number_of_dies
   model
         reference r on (select face_id, face_value, probability from die)
           dimension by (face_id)
           measures (face_value,probability)
         main m
           partition by (l rn, cnt)
           dimension by (0 i)
           measures (0 die_face_id, 0 sum_value, 1 prob, l remainder)
         rules iterate (1000) until (iteration_number + 1 = :N)
         ( die_face_id[0] = 1 + mod(remainder[0]-1,cv(cnt))
         , remainder[0]   = ceil((remainder[0] - die_face_id[0] + 1) / cv(cnt))
         , sum_value[0]   = sum_value[0] + face_value[die_face_id[0]]
         , prob[0]        = prob[0] * probability[die_face_id[0]]
         )
)
select sum_value "Sum"
     , sum(prob) "Probability"
  from all_probabilities
 group by rollup(sum_value)
 order by sum_value
/

update die
   set face_value = face_id
     , probability = 1/6
/

select * from die order by face_id
/

with number_of_dies as (select count(*) cnt from die)
, all_probabilities as
( select sum_value
       , prob
       , i
    from (select level l from number_of_dies connect by level <= power(cnt,:N))
       , number_of_dies
   model
         reference r on (select face_id, face_value, probability from die)
           dimension by (face_id)
           measures (face_value,probability)
         main m
           partition by (l rn, cnt)
           dimension by (0 i)
           measures (0 die_face_id, 0 sum_value, 1 prob, l remainder)
         rules iterate (1000) until (iteration_number + 1 = :N)
         ( die_face_id[0] = 1 + mod(remainder[0]-1,cv(cnt))
         , remainder[0]   = ceil((remainder[0] - die_face_id[0] + 1) / cv(cnt))
         , sum_value[0]   = sum_value[0] + face_value[die_face_id[0]]
         , prob[0]        = prob[0] * probability[die_face_id[0]]
         )
)
select sum_value "Sum"
     , sum(prob) "Probability"
  from all_probabilities
 group by rollup(sum_value)
 order by sum_value
/

exec :N := 4

with number_of_dies as (select count(*) cnt from die)
, all_probabilities as
( select sum_value
       , prob
       , i
    from (select level l from number_of_dies connect by level <= power(cnt,:N))
       , number_of_dies
   model
         reference r on (select face_id, face_value, probability from die)
           dimension by (face_id)
           measures (face_value,probability)
         main m
           partition by (l rn, cnt)
           dimension by (0 i)
           measures (0 die_face_id, 0 sum_value, 1 prob, l remainder)
         rules iterate (1000) until (iteration_number + 1 = :N)
         ( die_face_id[0] = 1 + mod(remainder[0]-1,cv(cnt))
         , remainder[0]   = ceil((remainder[0] - die_face_id[0] + 1) / cv(cnt))
         , sum_value[0]   = sum_value[0] + face_value[die_face_id[0]]
         , prob[0]        = prob[0] * probability[die_face_id[0]]
         )
)
select sum_value "Sum"
     , sum(prob) "Probability"
  from all_probabilities
 group by rollup(sum_value)
 order by sum_value
/