The SQL standard defines two ways to generate auto-increment values. First, there are identity columns as an extention to exact numeric types. The syntax is: "GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY". Second, the use of sequences in combination with triggers is standardized.

CREATETABLEt1(col1NUMBERPRIMARYKEY);CREATESEQUENCEsequence_nameSTARTWITH1INCREMENTBY1;CREATEORREPLACETRIGGERtrigger_nameBEFOREINSERTONt1FOREACHROWDECLAREmax_idNUMBER;cur_seqNUMBER;BEGINIF:NEW.col1ISNULLTHEN-- normal assignment of the next value in the sequence:NEW.col1:=sequence_name.NEXTVAL;ELSE-- or allow the user to specify the value, so must advance the sequence to matchSELECTGREATEST(COALESCE(MAX(col1),0),:NEW.col1)INTOmax_idFROMt1;WHILEcur_seq<max_idLOOPSELECTsequence_name.NEXTVALINTOcur_seqFROMDUAL;ENDLOOP;ENDIF;END;-- since Oracle 12.1:CREATETABLEt1(col1NUMBERGENERATEDBYDEFAULTASIDENTITY);