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 are the two parts of a procedure ?
What is difference between cursor and ref cursor?
What is implicit cursor in pl sql?
How does pl sql work?
How would you reference column values before and after you have inserted and deleted triggers?
what is cursor. write example of it. What are the attributes of cursor.
How to move files from one directory to another in pl sql?
hi,i plan to put experience on PLSQL ,can anyone suggest me for any institutes in bangalore or how to prepare for interviews
Is pl sql useful?
What are tables in sql?
Can procedure in a package be overloaded?
Types of joins ?