CODEWITHSUNDEEP
databaseoracle-databaseplsqltriggerspackage
Lisa14
Lisa14

Reputation: 15

Creating trigger throwing compilation error in PL/SQL

I am logged in as user ADMIN and executing PROCEDURE P1 to check and capture logs. I am getting an error in creating trigger

PROCEDURE P1
IS
BEGIN
INSERT statement
BEGIN
---
EXCEPTION

WHEN others THEN
  error_mgr.record_error();
 END P1;

AS ADMIN user does not have sufficient priveledge I logged in as another user EXTRACT and created package error_mgr

create or replace
PACKAGE BODY error_mgr
IS
PROCEDURE record_error
 IS
 PRAGMA AUTONOMOUS_TRANSACTION;
 l_code  INTEGER := SQLCODE;
 l_mesg VARCHAR2(32767) := SQLERRM; 
 BEGIN
 INSERT INTO EXTRACT.error_log 
(created_on,created_by,errorcode,callstack,errorstack,backtrace,error_info) 
 VALUES(SYSDATE,USER,l_code,sys.DBMS_UTILITY.format_call_stack,null,
  sys.DBMS_UTILITY.format_error_backtrace,l_mesg);
        DBMS_OUTPUT.PUT_LINE('ERROR!!');  
COMMIT;
END record_error;
END error_mgr;

NOTE: EXTRACT is also username and schema

ERROR LOG table which is in extract schema has a auto generate column id(LOG_ID) so in EXTRACT schema I am using sequence and trigger

  SEQUENCE:
   CREATE SEQUENCE "EXTRACT"."SEQ_error_log" MINVALUE 1 MAXVALUE 
  9999999999999999999999999999 INCREMENT BY 1 START WITH 335 CACHE 20 NOORDER 
CYCLE ;

TRIGGER:

 CREATE TRIGGER EXTRACT.BI_error_log
 BEFORE INSERT ON INO_EIR_EXTRACT.error_log
 REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW

 BEGIN
 -- Set column from sequence
 If (:NEW.LOG_ID IS NULL)
 THEN
  SELECT EXTRACT.SEQ_error_log.NEXTVAL INTO :NEW.LOG_ID FROM dual;
 END IF;
 END;

ERROR:

PL/SQL:SQL statement ignored
PL/SQL: ORA2289 sequence does not exist.

Although I already executed sequence, I am still getting this error.

Upvotes: 1

Views: 134

Answers (1)

Alex Poole
Alex Poole

Reputation: 191455

You have created the sequence with a quoted identifier: When you use an unquoted reference like SEQ_error_log Oracle looks in the data dictionary for a name that is all uppercase, 'SEQ_ERROR_LOG'. But you created it as

CREATE SEQUENCE "EXTRACT"."SEQ_error_log"

which means that it appears in the data dictionary exactly like that, there is nothing matching the uppsercase version, and that whenever you use it you have to use a quoted identifier too, with the name in exactly the same case:

If (:NEW.LOG_ID IS NULL)
THEN
  SELECT EXTRACT."SEQ_error_log".NEXTVAL INTO :NEW.LOG_ID FROM dual;
END IF;

or more simply:

If (:NEW.LOG_ID IS NULL)
THEN
  :NEW.LOG_ID := EXTRACT."SEQ_error_log".NEXTVAL;
END IF;

It would be better in the long run to recreate the sequence, if you you don't yet have any table rows populated using its values or know how to adjust its starting value to compensate, to not have a quoted identifier:

DROP SEQUENCE "EXTRACT"."SEQ_error_log";
CREATE SEQUENCE "EXTRACT"."SEQ_ERROR_LOG" START WITH 335;

Now, clearly I have still included double-quotes around the identifiers, but because the actual names are all uppercase and legal (see doc link right at the start) that is the same as saying:

CREATE SEQUENCE extract.seq_error_log START WITH 335;

and either way the sequence can be referred to either quoted as uppercase or unquoted in any case you like - extract.seq_error_log, EXTRACT.SEQ_ERROR_LOG, EXTRACT.SEQ_error_log, extract.SeQ_ErRoR_lOg, or whatever you prefer. The way you have it in your existing trigger code will then work too.

Personally I tend to do everything in lower case - but I'd suggest whatever you choose you still use it consistently, as coding style makes code easier to read and maintain.

Upvotes: 5

Related Questions