Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Query to find out rows deleted from a table

ORA_AJ
Member Posts: 2,295
Hi
Suppose i have a table with a sequence i.e its an header table containing GRN nos.Now suppose someone has deleted some of serials from it like i am having 1,2,4,5,6 etc 3 is missing.Now what will be the query to find out what are the serials that are missed out in the table.
--Ajay Sharma
Suppose i have a table with a sequence i.e its an header table containing GRN nos.Now suppose someone has deleted some of serials from it like i am having 1,2,4,5,6 etc 3 is missing.Now what will be the query to find out what are the serials that are missed out in the table.
--Ajay Sharma
Answers
-
Try this one
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
Regards,
Mahesh Kaila -
Dear Mahesh
In my case Number of records is not fixed i mean to say its not neccesary that data is there for 1-6 only.It can be upto 100..
--Ajay Sharma -
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>
- Mahesh Kaila -
What if i delete 4,5 and 6 will your query find it?
-
I like recursive with clause B-)
Hehe I used PostgreSQL8.4 because I do not have Oracle11gR2WITH 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
-
Hi mahesh
I am using Oracle 8.1.5.0 and whenever i uses your query containing Connect by level I gets an errror saying Ora-01436 : CONNECT BY loop in user data..
Any comments about this.
--Ajay Sharma -
CONNECT BY LEVEL was not available in 8.1.5. In 8.1.5, the simplest solution would be to select a ROWNUM from a table that has more rows than the TEST table. Assuming that DBA_OBJECTS is sufficiently large
SELECT rownum id FROM dba_objects MINUS SELECT id FROM test
As an FYI, if you're using an ancient version of Oracle like 8.1.5, it's helpful to mention that up front. There have been a lot of advances in the 8 major releases since then.
Justin -
Mahesh Kaila wrote:Where can I find the related documentation? It has been solved, but why's that? Many thanks to your <A class=bodylinkwhite href="http://www.software-to-convert.com/myspace-video-conversion-software/myspace-video-to-iriver-lplayer-software.html"><FONT face=tahoma,verdana,sans-serif color=#000 size=1>explanation</FONT></A>!
Try this one
<PRE class=jive-pre><CODE class="jive-code jive-java">WITH test AS (SELECT <FONT color=navy>'1'</FONT> id
FROM DUAL
UNION ALL
SELECT <FONT color=navy>'2'</FONT> id
FROM DUAL
UNION ALL
SELECT <FONT color=navy>'4'</FONT> id
FROM DUAL
UNION ALL
SELECT <FONT color=navy>'5'</FONT> id
FROM DUAL
UNION ALL
SELECT <FONT color=navy>'6'</FONT> 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
</CODE></PRE>
Regards,
Mahesh Kaila
This discussion has been closed.