0

I am creating DDL scripts to create a DB model based on an existing DB. Basically polishing what I got from expdp.

As the DB is Oracle 11gR2, it does not support the 12c Identity Columns yet (https://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1). Therefore the exported statements use sequences-triggers like mentioned in https://community.oracle.com/thread/3677631?start=0&tstart=0 -- so maybe it was originally converted from MS SQL Server, I'm note sure.

The triggers look similarly to

create or replace TRIGGER "TABLEX_TRG" BEFORE INSERT ON TABLEX
FOR EACH ROW
DECLARE
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
  IF INSERTING AND :new.TABLEXId IS NULL THEN
    SELECT  TABLEX_TABLEXId_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    -- If this is the first time this table have been inserted into (sequence == 1)
    IF v_newVal = 1 THEN
      --get the max indentity value from the table
      SELECT NVL(max(TABLEXId),0) INTO v_newVal FROM TABLEX;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT TABLEX_TABLEXId_SEQ.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
  -- save this to emulate @@identity
  utils.identity_value := v_newVal;
   -- assign the value from the sequence to emulate the identity column
   :new.TABLEXId := v_newVal;
  END IF;
END;

The problem is that SQL Developer complains with "Error(21,3): PLS-00201: identifier 'UTILS.IDENTITY_VALUE' must be declared"

Strangely when I connect to the original DB and check the trigger, it is the same, no declaration or whatever of 'UTILS.IDENTITY_VALUE'. And is nicely green.

Any explanation/advice welcome!

Radek Skokan
  • 1,358
  • 2
  • 15
  • 38

1 Answers1

1

Most likely, there should be a package called UTILS that is missing on your new system; you have to copy it from the existing system, too. It is also possible that the package exists, but you are not seeing it (missing synonym) or have no privilege to access it.

If nothing else is necessary from UTILS (rather unlikely but possible), you can do the following: 

 create package UTILS as
    identity_value number(12);
 end UTILS;
 /
Erich Kitzmueller
  • 36,381
  • 5
  • 80
  • 102