Oracle Sequences
- by jkrebsbach
Reminder to myself -
SQL Server has nice index columns directly tied to their tables.
Oracle has sequences that are islands to themselves.
select seq_name.currval from dual;
select seq_name.nextval from dual;
currval - return current number at top of sequence
nextval - increment sequence by 1, return new number
therefore - to create functionality in oracle similar to an index column -
OPTION A) - Create insert trigger:
CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
SELECT dept_seq.NEXTVAL
INTO :new.id
FROM dual;
END;
This will handle creating a unique identity, but will not necessarily inform process
flow of identity without additional logic.
OPTION B) - Select indentity into temp variable, insert whole item into tab
****
When attemptint to query currval, the below error was being thrown -
SELECT seq_name.currval from dual;
ERROR : TABLE OR VIEW DOES NOT EXIST
***
Although Oracle sys tables may have access to the sequences, that isn't to say the Oracle user may have access to those sequences - verify permissions when the system can't see object that are being reported in the object explorer.
