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;

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;

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

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;
/

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;

Answer / sureshramsing@gmail.com

select level from dual connect by level<=100 minus select <column name> from <table name>;

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;

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));

Answer / dipanshu saini

select level from dual connect by level<=(select max(seq_no) from test_tab)
minus
select seq_no from test_tab;

Explain the savepoint statement.

0 Answers  

Why do we use procedures in sql?

0 Answers  

Can a procedure in a package be overloaded?

0 Answers   EXL,

What is the order of sql select?

0 Answers  

how to create a table index in mysql? : Sql dba

0 Answers  

How to write html code in pl sql?

0 Answers  

ERROR:Insert or update on table"accnt" violates foreign key constraints "acct_to_curr_symbol" DETAILS:KEY(accnt_curr_id)(-2)is not present in the table "curr_symbol" ......solve The Problem..

0 Answers   Wipro,

When to use inner join and left join?

0 Answers  

how will i became good database developer?

1 Answers  

4. Select sum(A.salary) +sum(B.salary) as TOT_SAL from ( select LEVEL emp_id,level*100 salary,case when mod (level,2)=0then 2 else null end dept_id from dual connect by level<6 )A right outer join (select level emp_id ,level*200 salary ,case when mod (level,3)=0 then 2 else null end dept_id from dual connect by level<6)B On A.dept_id=B.dept_id And A.emp_id=B.emp-id;

1 Answers   Fintellix,

What is different between union and minus?

7 Answers   Accenture, TCS,

What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?

4 Answers