If there are 1 to 100 numbers in a table and in that 100
numbers some 10 numbers are deleted.I want to find out the
missing numbers between 1 to 100 by pl/sql how?
Answers were Sorted based on User's Feedback
Answer / ravi kumar singh
DECLARE
RETVAL NUMBER:=0;/*A_CHK IS TABLE AND A IS COLOUMN HAVING NOS*/
I NUMBER:=0;
BEGIN
FOR I IN 1..100
LOOP
SELECT COUNT(*) INTO RETVAL FROM A_CHK WHERE A=I;
IF (RETVAL=0) THEN
DBMS_OUTPUT.PUT_LINE(I);
ELSE NULL;
END IF;
END LOOP;
END;
Is This Answer Correct ? | 15 Yes | 0 No |
Answer / neha sinha
create or replace procedure pro1(cur_diff OUT SYS_REFCURSOR) is
BEGIN
OPEN cur_diff FOR
select diff
from (select /*+ no_merge */
rownum as diff
from (select 1 from dual group by cube(1, 1,
1, 1, 1)) a,
(select 1 from dual group by cube(1, 1,
1, 1, 1, 1)) b)
where diff >= (select min(lst) from list1)
and diff <= (select max(lst) from list1)
minus
select lst from list1;
end pro1;
-- where list1 is table name and lst is column in which
--some no is missing
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / kavitha n
BEGIN
FOR DATA IN (SELECT LEVEL num
FROM DUAL
CONNECT BY LEVEL <= 100
MINUS
SELECT num
FROM missingsquence)
LOOP
exit when sql%notfound;
DBMS_OUTPUT.put_line (DATA.num);
END LOOP;
END;
/
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / sumit
declare i number :=1;
begin
a number;
while (i<=100) loop
select num from table into a where num=i;
If a is null then dbms_output.put_line(i);
i:=i+1;
end loop;
end;
Is This Answer Correct ? | 2 Yes | 1 No |
Answer / subrat ray
SQL> desc numtab;
Name Null? Type
------ ------- ------
X NUMBER(4)
declare
v_num number(2);
cursor c1 is select rownum num from all_tables where rownum<=100
minus
select x num from numtab;
begin
open c1;
loop
fetch c1 into v_num;
exit when c1%notfound;
dbms_output.put_line(v_num);
end loop;
close c1;
end;
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / sureshramsing@gmail.com
select level from dual connect by level<=100 minus select <column name> from <table name>;
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / mani
DECLARE
l_id NUMBER;/* Temp IS TABLE AND ID IS COLOUMN HAVING
Numbers*/
BEGIN
FOR I IN 1..100 LOOP
SELECT COUNT(*) INTO l_id FROM temp WHERE id=I;
IF (l_id = 0) THEN
DBMS_OUTPUT.PUT_LINE(I);
ELSE
NULL;
END IF;
END LOOP;
END;
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / vishnu
create table test100 (id number);
insert into test100
select level from dual connect by level <=100;
select id from (SELECT rownum id from test100) where id
not in (select ID from(select rownum,id from test100) where id!=decode(id,rownum,rownum+1,rownum));
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / dipanshu saini
select level from dual connect by level<=(select max(seq_no) from test_tab)
minus
select seq_no from test_tab;
Is This Answer Correct ? | 0 Yes | 0 No |
what is table? : Sql dba
What is Overloading of procedures ?
what is a table called, if it has neither cluster nor non-cluster index? What is it used for? : Sql dba
Is merge a dml statement?
Can we edit a view in sql?
what are the authentication modes in sql server? : Sql dba
How do I create a sql script?
What will be the output of the following String S = 1+2+"abc" S = ? String S1 = 1+2+"abc"+5+6 S1 = ?
What is Highwatermark?
in materialized view the structure will create immediately or not?
explain commit and rollback in mysql : sql dba
What is the maximum size of sqlite database?