Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    8

    Unanswered: Using sequences in Oracle Forms?

    I've created a sequence to represent a primary key as follows:

    CREATE SEQUENCE emp_seq
    INCREMENT BY 1
    START WITH 0
    MAXVALUE 999999
    MINVALUE 0
    cycle
    nocache
    order;

    In Oracle Forms 9i I would like the user to enter in all other info (like name, dob etc) and have them save it, with the primary key (called empid and of type number) being generated using the sequence above. The primary key is included in the data block but not displayed. I created a pre-insert trigger as follows:

    begin
    SELECT emp_seq.nextval
    INTO :emp.empid
    FROM dual;
    END;

    and added this to the emp data block.
    However when I run it, enter in test data and try and save it I get the following error "WHEN-VALIDATE-ITEM trigger failed on field - emp.empid" so its as if its not generating the sequential number.
    Not sure whats wrong so any help?

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: Using sequences in Oracle Forms?

    Originally posted by scomfort
    I've created a sequence to represent a primary key as follows:

    CREATE SEQUENCE emp_seq
    INCREMENT BY 1
    START WITH 0
    MAXVALUE 999999
    MINVALUE 0
    cycle
    nocache
    order;

    In Oracle Forms 9i I would like the user to enter in all other info (like name, dob etc) and have them save it, with the primary key (called empid and of type number) being generated using the sequence above. The primary key is included in the data block but not displayed. I created a pre-insert trigger as follows:

    begin
    SELECT emp_seq.nextval
    INTO :emp.empid
    FROM dual;
    END;

    and added this to the emp data block.
    However when I run it, enter in test data and try and save it I get the following error "WHEN-VALIDATE-ITEM trigger failed on field - emp.empid" so its as if its not generating the sequential number.
    Not sure whats wrong so any help?
    http://www.dbforums.com/t972816.html

  3. #3
    Join Date
    Dec 2003
    Posts
    8

    Re: Using sequences in Oracle Forms?

    Thanks for the reply.
    I saw what was written there and I had a similar trigger like that in SQL*Plus which was like:


    CREATE OR REPLACE TRIGGER BI_emp
    BEFORE INSERT ON emp
    FOR EACH ROW
    BEGIN
    SELECT emp_seq.NEXTVAL
    INTO :NEW.empID
    FROM DUAL;
    END;

    this works fine when I insert using SQL *Plus. However in Forms not so it would seem. Having looked on other sites I found you could create a pre-insert trigger to carry out the sequence on the primary key. I tried running but no luck so deleted the trigger above in case there was a conflict between the two but still didnt work. Obviously Im not doing something correctly?!

  4. #4
    Join Date
    Dec 2003
    Posts
    8

    Smile Re: Using sequences in Oracle Forms?

    Oh not to worry figured it out!
    Was supposed to use a pre-form trigger not a pre-insert trigger! Thanks anyway.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •