?
Solved

Oracle table insert new row question

Posted on 2011-04-20
12
Medium Priority
?
366 Views
Last Modified: 2012-05-11
I created an oracle (11gR2) table TAB1
with this command:
CREATE TABLE LCTB_REPORT_SRVC
   (      ID NUMBER,
      F1 VARCHAR2(20)
   );

Now I insert into this table:
(1, 'myval1')
(2, 'myval2')
...
...
and so on

Later if type select * from TAB1
The o/p does not show with id value ascending.

Is there any way that the select * from TAB1  will always show the o/p in id values ascending?
0
Comment
Question by:toooki
12 Comments
 
LVL 12

Expert Comment

by:enachemc
ID: 35430975
select * from TAB1 order by id
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35431045
hi
- your table is TAB1 or LCTB_REPORT_SRVC?

select * from TAB1 order by id ASC
OR
select * from LCTB_REPORT_SRVC order by id ASC
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35431049
- 'ASC' is ascending order
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:toooki
ID: 35431172
Thank you all for your replies.

My table is MYTAB1. Sorry.

I know select * from TAB1 order by id will give the O/P I want to see.

I inserted the values in order (I mean first inserted the row with id =1, then id=2 ...).

I was thinking if there was any way for it to show in correct id ascending order without the "order by id" clause in the select statement.
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35431256
- can you further explain on the "correct id ascending order?" is it based on the sequence when you insert the data?
0
 

Author Comment

by:toooki
ID: 35431299
No. I did not use sequence.
Just inserted the number values for ID column. As it is anyway a small table.

By correct order means .. just it looks awkward if select * and see the IDs not in order (when I inserted in order).
I know tables don't store records in order.

Otherwise, well maybe then there is no other way...
0
 
LVL 23

Assisted Solution

by:OP_Zaharin
OP_Zaharin earned 900 total points
ID: 35431302
- based on your data samples. if you insert the record in the following sequence:
(2, 'myval2')
(4, 'myval4')
(1, 'myval1')
(3, 'myval3')

- when we query this way: SELECT * from MYTAB1. it will display the data as it is
2, myval2
4, myval4
1, myval1
3, myval3

- when we query with the order by: SELECT * from MYTAB1 ORDER BY id ASC. then it will sort your data:
1, myval1
2, myval2
3, myval3
4, myval4

0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 900 total points
ID: 35432195
What I believe the others are saying through examples is:  In Oracle the ONLY way to guarantee sort order is to use an ORDER BY.

There are several white papers out there that talk about this.  Ton Kyte has a good article on this at:
http://tkyte.blogspot.com/2005/08/order-in-court.html
0
 
LVL 2

Expert Comment

by:spyresponse
ID: 35432691
according to u ur table is TAB1   and u  create table name of    CREATE TABLE LCTB_REPORT_SRVC
.... than u


 fetech a data from TAB1....
 thst is ur ist mistake ....


and ur 2 mistake is  U create a table table name TAB1 thst is reserve word in oracle 11g....
0
 
LVL 32

Expert Comment

by:awking00
ID: 35432759
Since when did TAB1 become a reserved word?
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35432782
- i'm wondering the same too.
- BTW toooki already corrected that his table name is MYTAB1 if you read the whole posting here :)
0
 

Author Comment

by:toooki
ID: 35436854
Thank you all.
I see some post there http://tkyte.blogspot.com/2005/08/order-in-court.html as slightwv mentioned.
Well it is not a huge issue for me. I asked just to see if there was an easy way for it.
Thanks!
 
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question