Home » SQL & PL/SQL » SQL & PL/SQL » insert values in table
insert values in table [message #8742] Tue, 23 September 2003 04:00 Go to next message
Bipul
Messages: 11
Registered: June 2003
Junior Member
How can Insert a values in between two rows in a table?
Re: insert values in table [message #8744 is a reply to message #8742] Tue, 23 September 2003 05:44 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Please have a look [url=http://www.orafaq.net/msgboard/sql/messages/20806.htm] here at our old posting [/url]

Re: insert values in table [message #8746 is a reply to message #8742] Tue, 23 September 2003 07:22 Go to previous message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Just a clarification :

Oracle doesnt return rows in any predictable order ,unless you explicitly sort them by a column. Ordering by ROWIDS may not give you consistent result.
ROWIDs CAN BE REUSED .
ROWS may NOT be returned according to the order of inserts ie)The Last record you inserted may be the 2nd row that is returned.

Pls see below :

SQL> create table t(a int,b varchar2(2));

Table created.

SQL> insert into t(a) values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select rowid,t.* from t;

ROWID A B
------------------ ---------- --
AAAHiAAAIAAABRWAAA 1

SQL> insert into t values(2,'a');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t values(3,'a');

1 row created.

SQL> commit;

Commit complete.

SQL> select rowid,t.* from t;

ROWID A B
------------------ ---------- --
AAAHiAAAIAAABRWAAA 1
AAAHiAAAIAAABRWAAB 2 a
AAAHiAAAIAAABRWAAC 3 a

SQL> delete from t where a=2;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select rowid,t.* from t;

ROWID A B
------------------ ---------- --
AAAHiAAAIAAABRWAAA 1
AAAHiAAAIAAABRWAAC 3 a

SQL> insert into t values(4,'a');

1 row created.

SQL> commit;

Commit complete.

SQL> select rowid,t.* from t;

ROWID A B
------------------ ---------- --
AAAHiAAAIAAABRWAAA 1
AAAHiAAAIAAABRWAAC 3 a
AAAHiAAAIAAABRWAAD 4 a

SQL> select a from t where rownum=1 order by rowid desc;

A
----------
1

SQL> select a from ( select * from t order by rowid desc) where rownum=1;

A
----------
4

SQL> insert into t values(5,'a');

1 row created.

SQL> commit;

Commit complete.

SQL> select rowid,t.* from t;

ROWID A B
------------------ ---------- --
AAAHiAAAIAAABRWAAA 1
AAAHiAAAIAAABRWAAB 5 a
AAAHiAAAIAAABRWAAC 3 a
AAAHiAAAIAAABRWAAD 4 a

-- You see that the last row you inserted (ie a=5) was inserted into somewhere in the middle.Infact -- it has 'REUSED' the rowid that was earlier used by a=2. The 'deleted' ROWID has come back!

SQL> insert into t values(6,'a');

1 row created.

SQL> commit;

Commit complete.

SQL> select rowid,t.* from t;

ROWID A B
------------------ ---------- --
AAAHiAAAIAAABRWAAA 1
AAAHiAAAIAAABRWAAB 5 a
AAAHiAAAIAAABRWAAC 3 a
AAAHiAAAIAAABRWAAD 4 a
AAAHiAAAIAAABRWAAE 6 a

SQL> select * from t order by rowid desc;

A B
---------- --
6 a
4 a
3 a
5 a
1

SQL> select * from t where rownum < 3;

A B
---------- --
1
5 a
-- As you see,the 5th record you inserted is the 2nd row that is returned. Since the 2nd rowid was free ,it got reused..but again this may not always be the case.

Try to rely on sequences,time values,column groups,keys etc for the order you want.

Thiru
Previous Topic: Can I call a exe in trigger in ProC? How?
Next Topic: using of rowid
Goto Forum:
  


Current Time: Thu Mar 28 07:00:34 CDT 2024