For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
WITH test AS (SELECT '1' id FROM DUAL UNION ALL SELECT '2' id FROM DUAL UNION ALL SELECT '4' id FROM DUAL UNION ALL SELECT '5' id FROM DUAL UNION ALL SELECT '6' id FROM DUAL) SELECT TO_CHAR (l) FROM (SELECT LEVEL l FROM DUAL CONNECT BY LEVEL <= (SELECT MAX (id) FROM test)) MINUS SELECT id FROM test
SELECT TO_CHAR (l) FROM (SELECT LEVEL l FROM DUAL CONNECT BY LEVEL <= (SELECT MAX (id) FROM <YOUR_TABLE>)) MINUS SELECT id FROM <YOUR_TABLE>
WITH recursive t(Val) AS ( values(1),(2),(4),(5),(6),(12)), rec(Val,LeadVal) as( select Val+1,LeadVal from (select Val,Lead(Val) over(order by Val) as LeadVal from t) tmp where Val+1 < LeadVal union all select Val+1,LeadVal from rec where Val+1 < LeadVal) select Val from rec; val ----- 3 7 8 9 10 11
WITH test AS (SELECT '1' id FROM DUAL UNION ALL SELECT '2' id FROM DUAL UNION ALL SELECT '4' id FROM DUAL UNION ALL SELECT '5' id FROM DUAL UNION ALL SELECT '6' id FROM DUAL) SELECT TO_CHAR (l) FROM (SELECT LEVEL l FROM DUAL WHERE LEVEL >= (SELECT MIN (id) FROM test) CONNECT BY LEVEL <= (SELECT MAX (id) FROM test)) MINUS SELECT id FROM test
SELECT rownum id FROM dba_objects MINUS SELECT id FROM test