Search code examples
sqloracleoracle-apexoracle-apex-5.1

Oracle Apex - ID column not incrementing based on the set sequence but rather to "t1000"


I have created a sequence in oracle apex and linked the sequence to the row on the application end. However, on the master-detail form, it doesn't auto increment to the new Roster_ID when I try to add a new form record. What could be my mistake? Please help

Here is my code:

CREATE TABLE ROSTER(
    Roster_ID NUMBER(5),
    Working_Hours NUMBER(2),
    Shift_Date DATE,
    Shift_End_Date DATE,
    Shift_ID NUMBER(5) REFERENCES Shift (Shift_ID),
    Ward_ID NUMBER(5) REFERENCES WARD (Ward_ID),
    Ward_Name VARCHAR2(25) REFERENCES WARD (Ward_Name),
    CONSTRAINT PK_ROSTER PRIMARY KEY (Roster_ID),
    CONSTRAINT CHECK_WORKING_HOURS CHECK (Working_Hours IN (6, 8, 12))
);

CREATE SEQUENCE Roster_seq START WITH 5 INCREMENT BY 1 NOCACHE NOCYCLE;

CREATE OR REPLACE TRIGGER Roster_T1
BEFORE
INSERT ON Roster
FOR EACH ROW
BEGIN
  IF :NEW.Roster_ID is NULL THEN 
    select Roster_seq.nextval into :NEW.Roster_ID from SYS.DUAL; 
  END IF; 
END;
/
COMMIT;
/

enter image description here


Solution

  • There's no need for a trigger; you could have set roster_id's default value to sequence roster_seq.

    Apart from that, set roster_id to be a

    • hidden column (so that it is invisible)
    • primary key
    • value protected

    If you then run a page and add a new row, Apex will let you enter all data you need, and save new row(s) along with the next sequence values in roster_id column.


    If you want to see roster_id's value, then create a new column, let's call it roster_id_display and set it to be

    • display only
    • its source is a database column named ROSTER_ID whose datatype is number
    • set its query only property to yes

    Now everything works: you can enter new rows and see IDs, but you can't (and shouldn't, anyway) change it via Interactive Grid.