Search code examples
sqloracleplsqloracle-sqldeveloper

PL/SQL Throwing Error ORA-02289: sequence does not exist (Oracle SQL)

I created a table using SQL Developer. In my table the ID column, I made it to be an auto incremental numeric column. When I get the SQL for the table from SQL tab in the SQL Developer and try to run the it in another instance of the DB, I get an exception:

SQL Developer Error Message

I am trying to run the script:

CREATE TABLE "GAL_ABS_DB"."DEVICE_LOCKING" 
(   "ID" NUMBER(*,0), 
"TECH" VARCHAR2(6 BYTE), 
"DEVICENAME" VARCHAR2(20 BYTE), 
"LOCKINGTYPE" VARCHAR2(16 BYTE), 
"LOCKINGDEFINITION" VARCHAR2(6 BYTE)
) SEGMENT CREATION IMMEDIATE 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "GAL_ABS_DB" ;

CREATE OR REPLACE TRIGGER "GAL_ABS_DB"."DEVICE_LOCKING_TRIGGER" 
BEFORE INSERT ON DEVICE_LOCKING 
FOR EACH ROW 
BEGIN
  <<COLUMN_SEQUENCES>>
  BEGIN
    IF INSERTING AND :NEW.ID IS NULL THEN
      SELECT DEVICE_LOCKING_SEQ.NEXTVAL INTO :NEW.ID FROM SYS.DUAL;
    END IF;
  END COLUMN_SEQUENCES;
END;
/
ALTER TRIGGER "GAL_ABS_DB"."DEVICE_LOCKING_TRIGGER" ENABLE;

Can you please tell me what this is complaining about? I got the script from SQL Developer itself.

Solution

  • Presumably the DEVICE_LOCKING_SEQ sequence object does not exist.

    This would be a standalone object in its own right and not, for example, part of a table. So you should have a CREATE SEQUENCE ... statement