Hi, I'm developing on a large codebase with an Oracle backend. Most people develop against an Oracle instance in the local office on the LAN, but I am remote, so I install, maintain and develop on my own local Oracle instance. I re-installed the latest 11gR2 enterprise the other day and ran the standard DDL scripts that everyone else runs, but I encounter some errors related to foreign keys (parent key not found on insert statements). The problem is with sequences and the starting values >
For example, here's the SQL that creates one of the sequences I am having problems with:
CREATE SEQUENCE APPLICATIONSEQ INCREMENT BY 1 START WITH 1;
This sequence is used to generate ids for a table called APPLICATION. The table is created right after the sequence and later on some DML is executed such as this:
INSERT INTO APPLICATION (id, name, description, forward)
VALUES (APPLICATIONSEQ.nextval, 'value', 'value', 'value');
The scripts I'm mentioning do the following in order: drop all tables, sequences, etc. -> create all tables, sequences, etc. -> DML (inserts). They are executed from SQL*Plus on the command line (a top-level script is called which calls other scripts in a specific order.)
So when the scripts start, it drops everything, including this sequence, so I'm expecting any insert operations to start with ID := 1, but they don't. When the inserts happen, the first INSERT into the APPLICATION table (applicationseq.nextval) uses the value 2 for the id.
This causes other issues, as there are some hard-coded references that expect the id to be 1 -- which is horribly wrong, I know, but I was not responsible for that.
The thing that baffles me is why my Oracle environment always starts with 2...I am the only person encountering this issue. If I create a sequence and select from it, it behaves as expected:
create sequence foobar start with 1 increment by 1;
select foobar.nextval from dual;
1
If I execute the drop scripts, I can validate that the sequence is gone. How is it that creating the sequence and then inserting into it starts at id 2? This database is not shared, there are no other users on it. It is a standalone edition of 11gR2 enterprise running on a laptop.
Any ideas on what might be going on here?