What's is wrong with my trigger?

Question

I'm using Oracle 12.2c and I need execute a trigger:

Audit table

CREATE TABLE "MY_USER"."AUDIT"
(
  "TABLE_NAME"  VARCHAR2(20 CHAR)       NOT NULL ENABLE,
  "REGISTER_ID" NUMBER(19, 0) DEFAULT 0 NOT NULL ENABLE,
  "OPERATION"   VARCHAR2(20 CHAR)       NOT NULL ENABLE,
  "DATETIME"    TIMESTAMP(6)            NOT NULL ENABLE,
  "PO_DOCUMENT" CLOB,
  CONSTRAINT "ENSURE_JSON" CHECK (po_document IS JSON) ENABLE
);

Trigger

CREATE OR REPLACE EDITIONABLE TRIGGER "MY_USER"."TRG_PARTNER"
AFTER DELETE OR INSERT OR UPDATE ON "MY_USER"."PARTNER"
FOR EACH ROW
DECLARE
  vOperation VARCHAR2(6);
  vTableName VARCHAR2(30) := 'PARTNER';
  vLOGON_TYPE CHAR(1);
  vLAST_UPDATE DATE;
  vSTATUS CHAR(1);
  vADMIN_PASSWORD VARCHAR2(255 CHAR);
  vPASSWORD VARCHAR2(255 CHAR);
  vSESSION_VALID_SECONDS NUMBER(10, 0);
  vONBOARDING CHAR(1);
  vID VARCHAR2(255 CHAR);
  vNAME VARCHAR2(255 CHAR);
  vNETWORK_CODE VARCHAR2(255 CHAR);

BEGIN

  IF INSERTING THEN
    vOperation := 'INSERT';
    vLOGON_TYPE := :NEW.LOGON_TYPE;
    vLAST_UPDATE := :NEW.LAST_UPDATE;
    vSTATUS := :NEW.STATUS;
    vADMIN_PASSWORD := :NEW.ADMIN_PASSWORD;
    vPASSWORD := :NEW.PASSWORD;
    vSESSION_VALID_SECONDS := :NEW.SESSION_VALID_SECONDS;
    vONBOARDING := :NEW.ONBOARDING;
    vID := :NEW.ID;
    vNAME := :NEW.NAME;
    vNETWORK_CODE := :NEW.NETWORK_CODE;

  ELSIF UPDATING THEN
    vOperation := 'UPDATE';
    vLOGON_TYPE := :NEW.LOGON_TYPE;
    vLAST_UPDATE := :NEW.LAST_UPDATE;
    vSTATUS := :NEW.STATUS;
    vADMIN_PASSWORD := :NEW.ADMIN_PASSWORD;
    vPASSWORD := :NEW.PASSWORD;
    vSESSION_VALID_SECONDS := :NEW.SESSION_VALID_SECONDS;
    vONBOARDING := :NEW.ONBOARDING;
    vID := :NEW.ID;
    vNAME := :NEW.NAME;
    vNETWORK_CODE := :NEW.NETWORK_CODE;

  ELSIF DELETING THEN
    vOperation := 'DELETE';
    vLOGON_TYPE := :OLD.LOGON_TYPE;
    vLAST_UPDATE := :OLD.LAST_UPDATE;
    vSTATUS := :OLD.STATUS;
    vADMIN_PASSWORD := :OLD.ADMIN_PASSWORD;
    vPASSWORD := :OLD.PASSWORD;
    vSESSION_VALID_SECONDS := :OLD.SESSION_VALID_SECONDS;
    vONBOARDING := :OLD.ONBOARDING;
    vID := :OLD.ID;
    vNAME := :OLD.NAME;
    vNETWORK_CODE := :OLD.NETWORK_CODE;

  END IF;

  INSERT INTO "MY_USER"."AUDIT" (TABLE_NAME, REGISTER_ID, OPERATION, DATETIME, PO_DOCUMENT)
  VALUES (
    vTableName,
    vID,
    vOperation,
    sysdate,
    json_object('logon_type' VALUE vLOGON_TYPE, 'last_update' VALUE vLAST_UPDATE, 'status' VALUE vSTATUS, 'admin_password' VALUE vADMIN_PASSWORD, 'password' VALUE vPASSWORD, 'session_valid_seconds' VALUE vSESSION_VALID_SECONDS, 'onboarding' VALUE vONBOARDING, 'id' VALUE vID, 'name' VALUE vNAME, 'network_code' VALUE vNETWORK_CODE)
  );

END;

After executed this trigger and try insert something I get an error. But if I drop this trigger and execute that same insert there's no problem.

[2017-10-10 10:38:08] [42000][1722] ORA-01722: invalid number

[2017-10-10 10:38:08] ORA-06512: at "MY_USER.TRG_PARTNER", line 58

[2017-10-10 10:38:08] ORA-04088: error during execution of trigger 'MY_USER.TRG_PARTNER'

What's is wrong with trigger? This is my insert:

INSERT INTO PARTNER
(ID, ADMIN_PASSWORD, LAST_UPDATE, LOGON_TYPE, NAME, ONBOARDING, PASSWORD, SESSION_VALID_SECONDS, STATUS, NETWORK_CODE)
VALUES ('example foo', 'foo', TO_DATE('2003/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'), 'T', 'foo', 'o', 'foo', 4344, 'A', 'foo');

And line 58 is vNETWORK_CODE VARCHAR2(255 CHAR); which isn't a number

Partner

CREATE TABLE PARTNER (
  ID                    VARCHAR2(255 CHAR) PRIMARY KEY,
  ADMIN_PASSWORD        VARCHAR2(255 CHAR) NOT NULL,
  LAST_UPDATE           DATE               NOT NULL,
  LOGON_TYPE            CHAR(1)            NOT NULL,
  NAME                  VARCHAR2(255 CHAR) NOT NULL,
  ONBOARDING            CHAR(1)            NOT NULL,
  PASSWORD              VARCHAR2(255 CHAR) NOT NULL,
  SESSION_VALID_SECONDS NUMBER(10, 0)      NOT NULL,
  STATUS                CHAR(1)            NOT NULL,
  NETWORK_CODE          VARCHAR2(255 CHAR),
  CONSTRAINT UK_PARTNER_NAME UNIQUE (NAME)
);

well,where is line 58? Error is very specific. Whatever value you have, it is not a valid number. — OldProgrammer
Not an Oracle specialist, but this " line 58" indicates the problem is somewhere in the last insert. I would write something to find the values you are getting for that specific statement. — HLGEM
Line 58 is vNETWORK_CODE VARCHAR2(255 CHAR); and isn't a number. — Daniela Morais
What is the datatype of AUDIT.REGISTER_ID? Does the error still happen when you remove the INSERT INTO "MY_USER"."AUDIT" statement from the trigger? — trincot

trincot trincot · Accepted Answer · 2017-10-10T14:22:41

From comments it turns out that AUDIT.REGISTER_ID has a number data type, while vID has a character type.

As a consequence that INSERT will fail with the error you mentioned.

One solution would be to change the data type of the REGISTER_ID column to VARCHAR2(255), so it can cope with strings.

What's is wrong with my trigger?

1 Answers