Comments
| AutoNumber And Identity Functionality - Implement AutoNumber or Identity column behaviour in Oracle. |
Praveen Sadasivam said... SQL Server allows us to create tables dynamically inside Stored procedures just by callingInsert 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!