Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Retrieving last created sequence index

Posted on 2002-05-29
6
1,858 Views
Last Modified: 2008-03-17
Let's say I have a table with 2 columns,

dogindex auto-generating sequence number
dogname varchar2(30)

If I insert a row into the table, how do I retrieve the last created index?  I am not looking for the following answer:

select max(dogindex) from dogtable

Rather, I'm looking for an Oracle equivalent of MS SQL Server's @@Identity system variable that will return the last created index.

Thanks.

0
Comment
Question by:BCao
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7042037
In Oracle I was used to query a new sequence number into a variable, and use that variable to create (insert) the new record. I could then use that variable to return to the application...

CHeers
0
 
LVL 2

Expert Comment

by:vbDoc
ID: 7042143
Ah, check out ROWNUM. The ROWNUM can also be used to update a table and to add a sequential number to each record. If a column named custiomer_id2 to  datatype NUMBER(7) was added to a table called s_customer, then the following update can be performed to uniquely number each record.

UPDATE s_customer
SET customer_id2 = ROWNUM;

HTH
John
0
 

Author Comment

by:BCao
ID: 7042156
Thanks guys for the replies, but unfortunately I cannot change the table design.

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Expert Comment

by:joekendall
ID: 7042165
You could retrieve the currval from the sequence which should show the last value inserted into the table. Something like this would work:

SELECT SequenceName.currval FROM dual;

Thanks!

Joe
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 75 total points
ID: 7042204
Oracle tables do not directly support an "auto-generated" column the same way the MS SQL Server does.

Usually in Oracle "index" numbers like this for key values are generated by an Oracle sequence (I'm not sure that MS SQL Server has an object like an Oracle sequence.)  Insert statements typically get the next value with syntax like "sequence_name.nextval".  Let's assume you created a sequence named dog_seq and your table is named "dog_table".  Then in SQL*Plus your insert statement would look like this:

insert into dog_table
values (dog_seq.nextval, '&dog_name');

You could use a very similar statement in any other tool, just by substituting an appropriate variable for '&dog_name'.

If you have referenced that sequence in a session (with an insert statement that included "dog_seq.nextval") then you can query the database directly to see the last index value created.  Simply:
select dog_seq.lastval from dual;

If you haven't referenced that sequence though in your current database session, the only way to get the last value would be to select the highwater value from the sys.seq$ table.  But you need DBA privilege to do that, and you need to join it to sys.obj$ to use the sequence name.  Also, if the sequence was set up to cache values in the SGA, this query will show the last value cached, which wis usually higher than the last value actually used.

The bottom line is, in Oracle this particular question is not as easy to answer as it is in MS SQL Server.  I would usually get it by:
select max(dogindex) from dog_table;

This query will be fast if the column is indexed.  This is not the best way for an application to get the next value though (by selecting the max and adding 1) since multiple users could issue that same select at the same time.  Sequences handle that concurrency issue for you.
0
 

Author Comment

by:BCao
ID: 7042221
Thanks, your thourough answer was very insightful.

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
help on oracle query 5 52
How do I call MySQL Stored Procedure from oracle using HS link ? 5 58
Select and Insert Query running slow 4 45
run sql script from putty 4 66
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

840 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