Entry

This is a very dummy question: Does Oracle support identity (autoincrement) fields?

Sep 2nd, 2003 13:12
Kin Chan, Jesus Sanchez,

Yes, it does, but in a rather awkward way if compared to how Microsoft
Access/SQL Server does it

They use something called sequences.
A sequence is a unique set of numbers, which you have to define

/*
** Creating a sequence
*/

CREATE SEQUENCE USER_ID
START WITH 0
INCREMENT BY 1



         --
         -- TBL_USER
         -- This function retrieves the value from a sequence, &
increments it each time a new entry is made
         --
         CREATE OR REPLACE FUNCTION    iUSER
         (
              GENDER               TBL_USER.GENDER%TYPE,
              USER_NAME            TBL_USER.USER_NAME%TYPE,
              USER_PASSWORD        TBL_USER.USER_PASSWORD%TYPE,
              FULL_NAME            TBL_USER.FULL_NAME%TYPE,
              TITLE                TBL_USER.TITLE%TYPE,
              FORENAME             TBL_USER.FORENAME%TYPE,
              SURNAME              TBL_USER.SURNAME%TYPE,
              SUFIX                TBL_USER.SUFFIX%TYPE,
              SALUTATION           TBL_USER.SALUTATION%TYPE,
              EMAIL                TBL_USER.EMAIL%TYPE,
              PHONE                TBL_USER.PHONE%TYPE,
              MOBILE               TBL_USER.MOBILE%TYPE,
              FAX                  TBL_USER.FAX%TYPE,
              LANGUAGE_ID          TBL_USER.LANGUAGE_ID%TYPE,
              DATE_FORMAT_ID       TBL_USER.DATE_FORMAT_ID%TYPE,
              CURRENCY_ID          TBL_USER.CURRENCY_ID%TYPE
         )
         RETURN NUMBER AS
                USER_ID NUMBER;
             BEGIN
              SELECT sUSER_ID.NEXTVAL INTO USER_ID  FROM DUAL;
              INSERT INTO TBL_USER VALUES 
                   (
                    USER_ID,
                    GENDER,            
                    USER_NAME,           
                    USER_PASSWORD,     
                    FULL_NAME,           
                    TITLE ,              
                    FORENAME,           
                    SURNAME,            
                    SUFIX,        
                    SALUTATION,           
                    EMAIL,      
                    PHONE,            
                    MOBILE,         
                    FAX,        
                    LANGUAGE_ID,       
                    DATE_FORMAT_ID,      
                    CURRENCY_ID,  
                    SYSDATE          
                   );
              RETURN USER_ID;
         END iUSER;