Aggregator | Books | Industry News | Firefox Plugins | Social | Links

Comments

AutoNumber And Identity Functionality (Pre 12c) - Implement AutoNumber or Identity column behaviour in Oracle (Pre 12c).



Praveen Sadasivam said...

SQL Server allows us to create tables dynamically inside Stored procedures just by calling

Insert into Target select * from Source

Another feature SQL server allows here is

Insert into Target select *,col1 = identity(int,1,1) from Source


This will create one more column named 'col1' of type identity. This is done on the fly. Can this be achieved in oracle.

Tim... said...

Hi.

Please ask technical questions in the forum. The comments are for comments about the article contents.

Cheers

Tim...

Pratibha Enjeti said...

Thanks for enlightening about IDENTITY equivalent code in Oracle, I am used to programming in SQL Server and recently have switched to using Oracle. Your explanations and examples have helped me and have started giving me some faith that Oracle is not all that bad. Still can't get used to all the : (Semi-colons) that Oracle- PL/SQL requires to use!!
Thanks for sharing your knowledge.

Tim... said...

It's all the same, but different... :)

Cheers

Tim...


Al said...

Nice article, clear and concise. This gives our team an alternative to awkward get next id routines. Many thanks.

Raman said...

Thanks, very helpful

Hugo dimas said...

This a very important article, you can resolve my problem in 1 minute, I am a Sql Server User, and actually I am woprking DataBase in Oracle and this article resolve a pretty important problem in my code. thank you so much !!!

Grettings from Monterrey Mexico...!!!

Carol said...

Thanks very much, I was looking for this, it solved my problems.

Alemao said...

Thanks a lot. I´m a Oracle newbie and I was searching for extactly this feature.
Very very helpful.

so long


Dom said...

Exactly what I needed.

Thanks

greg said...

but not so easy to retrieve the identity value as you insert, i.e. in sql server you can do insert into table.... ;select @@identity as value, and then use that if you are inserting child rows in another table that reference the row inserted

Tim... said...

Hi.

Oracle has the RETURNING clause for all DML so you can return an ID value for a row you have inserted, updated or deleted.

If your operation touched multiple rows you have return multiple values into a collection.

Just as easy in Oracle as SQL Server. Just a different syntax. :)

Cheers

Tim...

Tim... said...

By the way, if you need any help with the syntax or approach, ask a question in the forum and I'll post source code etc.

Cheers

Tim...

Tim... said...

I just wrote an article explaining the RETURING INTO clause.

http://www.oracle-base.com/articles/misc/dml_returning_into_clause.php

Cheers

Tim...

Tom said...

To the person abover about the @@identity, you could manually insert seq_name.nextval and then use seq_name.currval to insert into child tables.

Tim... said...

True. They could also select the value of the sequence from dual, then use it for the inserts of the parent and child tables. :)

Cheers

Tim...

Tim... said...

I guess the issue here is, if the DBA has set up the autonumber functionality, you might not know what sequence supports the PK, so the return may be more convenient.

Cheers

Tim...

David said...

I too am from the sql server world. now working in Oracle (11g).. Lets say your inserting 10 mil rows. Would a before insert trigger have a big performance impact?

Tim... said...

Hi.

From a performance perspective, it is much better to put the call to sequence-name.NEXTVAL into the insert statement itself. The trigger will certainly be an extra overhead. The issue here is, if you need the autonumber functionality because you application (written for another engine) expects it, then you can do it.

Cheers

Tim...

George Garcia said...

This has been very helpful, thanks

DO NOT ask technical questions here, that's what my forum is for!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!

Add your comments here.
Name
Comment
(max 400 chars - plain text)