Q: How get next number some like sequence - Oracle PLSQL Hello in table I have data not use sequence (Row where num = 4 is deleted), How get number 4 for next insert? After insert row where num = 4, next num is 6...
eg.
Select 1 as num from dual
union all
Select 2 as num from dual
union all
Select 3 as num from dual
union all
Select 5 as num from dual


A: Try this one out:
WITH dat AS
(
SELECT 1 AS NUM
  FROM DUAL
 UNION ALL
SELECT 2 AS NUM
  FROM DUAL
 UNION ALL
SELECT 3 AS NUM
  FROM DUAL
 UNION ALL
SELECT 5 AS NUM
  FROM DUAL
)
SELECT d1.num + 1 AS next_val
  FROM dat d1
 WHERE NOT EXISTS (SELECT NULL
                     FROM dat d2
                    WHERE d2.num = d1.num + 1)
 ORDER BY next_val;

