Oracle sucks. Well, not totally, but it fails in a lot of places where MySQL is sexy. One little gem of an irritation is the lack of an auto_increment attribute associated with fields. Instead you have to make use of Oracle Sequences/Triggers which adds a whole layer of complexity on the creation and insertion into a simple table.
I’m not quite sure on this statement’s efficiency, but here’s my solution:
INSERT INTO table (id,name) (SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, ‘bork’ FROM table);
EDIT: It seems that this isn’t such a good idea, after help from my friendly neighborhood DBA.
Great work man,was a very useful help for me.
This statement can be used even when we are using VB6,oracle connectivity
Sorry, no.
This won’t work in an envioronment where more than one transaction is open at once. That means you should never ever use it because it’s bad practice and not scalable. (You never really know where it will end up.)
If two users do this at the nerly same time, more specifically if a second user does their insert after the first but before the first user commits, the second commit will fail with a constraint violation. That is, unless the column isn’t unique, in which case you’ll just get duplicate values.
Yup…I know that already. Read the post again and note the last line ;) Thanks anyhow.
I don’t understand. This problem can be solved by using sequences. But what if two overlapping transactions want to add a value to a field. One of the two updates will get lost?
I agree with you that Oracle really sucks. MySQL is a better choice.
MySQL also sucks at the time of Relation database Modeling with references but thats 100 times easier that this auto increamentation technique of Oracle.
So I think PostgreSql is easiest and Simplest and the best one
can host a Huge ammount of Data like Oracle
NOT a MEMORY HOG Like Oracle
and Its SQL is easier that anything else.
Not much fun of triggers, but Oracle triggers is total crap.
Because of FOR EACH ROW
Disgusting.
How hard from Oracle to just add the keyword AUTO_INCREMENT or IDENTITY to the identity field? and forget about this sequence/trigger crap?
/* Create its trigger */
CREATE SEQUENCE TIME_COMPANY_COMPANYID_SEQ
START WITH 1
INCREMENT BY 1
/* My table is **/
CREATE TABLE TIME_COMPANY
(
COMPANYID integer NOT NULL ,
COMPANYNAME varchar2 (100) NOT NULL
)
/* And its trigger would be like */
CREATE OR REPLACE TRIGGER TIME_COMPANY_TR
BEFORE INSERT ON TIME_COMPANY
FOR EACH ROW
DECLARE TEMP_NO INT;
BEGIN
SELECT TIME_COMPANY_COMPANYID_SEQ.NEXTVAL INTO :NEW.COMPANYID FROM DUAL;
END;
Regards
Adnan Hashmi.