Answer / poornima

using cursor we find the gap in sequence, but this method
is quite complicated and slow process...

so we use some analyical fuctions such as lead and lag as

{LEAD | LAG} (value_expression, offset, default)
OVER ([PARTITION BY expr] ORDER BY expr)

Answer / harikanthkoneti

yes...yes........ you can do it simply by updating that
table using rownum yar. see my example below.
SQL> create table test(sno number(2),sname varchar2(20));

Table created.

now if you have the data in the table like follows.

SQL> select * from test;

SNO SNAME
---------- --------------------
1 sdf
3 sdf
6 ewr
7 rt
90 drt

now just do the following simple statement.


SQL> update test set sno=rownum;

5 rows updated.


now here is the o/p for eliminating the gap generated by the
sequence.

SQL> select * from test;

SNO SNAME
---------- --------------------
1 sdf
2 sdf
3 ewr
4 rt
5 drt
then do the commit. thats all....................
now enjoy with this coding


suppose if you want to allocate the with any kind of series
of values for updating the table just follow the below
procedure

SQL> update test set sno=sql.nextval;

NOTE:sql is the sequence.

this statement will updates ur table with
what ever you given with the start with value and it ll
increment by which value you had given in the increment by
clause.

What happens if variable names collide with table/column names?

0 Answers  

---

Difference between an implicit & an explicit cursor.?

2 Answers  

---

What are the different types of modules in oracle forms?

0 Answers  

---

how many outer joins are used in a single sql query? is there any limitations for using of outer joins?

2 Answers   BOB Technologies, Cap Gemini, IBM,

---

How will you identify oracle database software release?

0 Answers  

---

23. Display the client name for all clients who have placed an order where any order line has more than 3 items. Do not use a table join anywhere in your query.

3 Answers   Wipro,

---

How would you best determine why your MVIEW couldnt FAST REFRESH?

0 Answers  

---

when a grant option is encountered for a table EMP to a peer sitting beside you who has already having the table of that name (EMP), then what is the result?

2 Answers  

---

what is reindexing?

0 Answers   Tata Technologies,

---

src name sex a,male b,female c,male d,female Required output : male female a,b c,d tried pivot but was not successfull select * from src pivot (max(name) for sex in ('MALE','FEMALE'));

0 Answers   IBM,

---

3. Display all the client numbers in the ORDER table. Remove duplicates.

1 Answers   Wipro,

---

sql query to get zero records from a table

7 Answers   CTS,

---