ORA-02289: sequence does not exist
Hi all. ORACLE Newbie alert!
I am using TOAD for Oracle 8.6
The Oracle database version is
BANNER
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
I have been tasked with monitoring a table from another schema, using a different account.
The first thing I did was create this table in my SOMERSET schema
CREATE TABLE MONITOR_PERSONS
(
IDENTIFIER INTEGER,
OLD_FAMILY_NAME VARCHAR2(40 BYTE),
OLD_FIRST_NAMES VARCHAR2(40 BYTE),
NEW_FAMILY_NAME VARCHAR2(40 BYTE),
NEW_FIRST_NAMES VARCHAR2(40 BYTE),
UPDATE_DATE DATE,
UPDATE_STATUS CHAR(1 CHAR),
PERSONS_IDENTIFIER INTEGER
)
then a SEQUENCE to be used by the above table
CREATE SEQUENCE SOMERSET.MONITOR_PERSONS_SEQ
START WITH 1
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;
then a trigger to watch the other schema's table and insert a record in my MONITOR_PERSONS table
CREATE OR REPLACE TRIGGER PERSONS_AFTER_UPDATE
AFTER UPDATE
OF FAMILY_NAME
,FIRST_NAMES
ON SWIFT_OWNER.PERSONS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
INSERT INTO SOMERSET.MONITOR_PERSONS
(
IDENTIFIER,
OLD_FAMILY_NAME,
OLD_FIRST_NAMES,
NEW_FAMILY_NAME,
NEW_FIRST_NAMES,
UPDATE_DATE,
UPDATE_STATUS,
PERSONS_IDENTIFIER)
VALUES
(
SOMERSET.MONITOR_PERSONS_SEQ.nextval,
:OLD.FAMILY_NAME,
:OLD.FIRST_NAMES,
:NEW.FAMILY_NAME,
:NEW.FIRST_NAMES,
sysdate,
'U',
:OLD.IDENTIFIER);
END PERSONS_AFTER_UPDATE;
/
Which compiles with this error
18/11 PLS-00302: component 'MONITOR_PERSONS_SEQ' must be declared
I have been around the net a number of times, and I just can't figure out why a sequence that exists in the same schema as the trigger is causing this issue.
Can someone help please?