1

Environment Oracle 19c (also 12c)

Here's my code:

CREATE OR REPLACE TRIGGER ClientTime
AFTER INSERT OR UPDATE OR DELETE ON ClientTime
FOR EACH ROW
DECLARE
  v_pk_json CLOB;
  v_changed CLOB;
  v_old_part VARCHAR2(32767);
  v_new_part VARCHAR2(32767);
  v_first_old BOOLEAN;
  v_first_new BOOLEAN;
BEGIN
  -- build pk json
  v_pk_json := '{' || 
    CASE 
      WHEN INSERTING OR UPDATING THEN '"id":' || NVL(TO_CHAR(:NEW.ID),'null') 
      ELSE '"id":' || NVL(TO_CHAR(:OLD.ID),'null') 
    END || 
  '}'; 

  -- build changed columns json
  IF INSERTING THEN  
    v_changed := '{' || 
      '"id":' || NVL(TO_CHAR(:NEW.ID), 'null') || ',' || 
      '"client":' || NVL(TO_CHAR(:NEW.CLIENT), 'null') || ',' || 
      '"timestamp":"' || NVL(TO_CHAR(:NEW.DATETIME, 'YYYY-MM-DD HH24:MI:SS'), 'null') || '"' ||
    '}';

  ELSIF DELETING THEN
    v_changed := '{' || 
      '"id":' || NVL(TO_CHAR(:OLD.ID), 'null') || ',' || 
      '"client":' || NVL(TO_CHAR(:OLD.CLIENT), 'null') || ',' || 
      '"timestamp":"' || NVL(TO_CHAR(:OLD.DATETIME, 'YYYY-MM-DD HH24:MI:SS'), 'null') || '"' ||
    '}'; 

  ELSE -- UPDATE: only changed columns
    v_old_part := '{';
    v_new_part := '{';
    v_first_old := TRUE;
    v_first_new := TRUE;

    IF NVL(:OLD.CLIENT, -999999) <> NVL(:NEW.CLIENT, -999999) THEN
      v_old_part := v_old_part || '"client":' || NVL(TO_CHAR(:OLD.CLIENT),'null');
      v_new_part := v_new_part || '"client":' || NVL(TO_CHAR(:NEW.CLIENT),'null');
      v_first_old := FALSE;
      v_first_new := FALSE;
    END IF;

    IF NVL(:OLD.DATETIME, TO_DATE('1900-01-01','YYYY-MM-DD')) <> NVL(:NEW.DATETIME, TO_DATE('1900-01-01','YYYY-MM-DD')) THEN
      IF NOT v_first_old THEN
        v_old_part := v_old_part || ',';
        v_new_part := v_new_part || ',';
      END IF;
      v_old_part := v_old_part || '"timestamp":"' || NVL(TO_CHAR(:OLD.DATETIME,'YYYY-MM-DD HH24:MI:SS'),'null') || '"';
      v_new_part := v_new_part || '"timestamp":"' || NVL(TO_CHAR(:NEW.DATETIME,'YYYY-MM-DD HH24:MI:SS'),'null') || '"';
    END IF;

    v_old_part := v_old_part || '}';
    v_new_part := v_new_part || '}';
    v_changed := '{"old":' || v_old_part || ',"new":' || v_new_part || '}';
  END IF;
  -- insert into audit_log
  INSERT INTO audit_log(change_txid, scn, change_ts, db_user, session_id, client_host, module_name,
                        schema_name, table_name, op_type, pk_json, changed_columns)
  VALUES (DBMS_TRANSACTION.LOCAL_TRANSACTION_ID, ORA_ROWSCN, SYSTIMESTAMP,
          SYS_CONTEXT('USERENV','SESSION_USER'), SYS_CONTEXT('USERENV','SESSIONID'),
          SYS_CONTEXT('USERENV','HOST'), SYS_CONTEXT('USERENV','MODULE'),
          'DBNAME','CLIENTTIME',
          CASE WHEN INSERTING THEN 'I' WHEN UPDATING THEN 'U' ELSE 'D' END,
          v_pk_json, v_changed);

END;

Disclaimer: I changed the columns names for security reasons. Datetime is not actually the column name, so that is not an oracle keyword in my use case.

The error (removed the lines, because I can't find any reason why that exact line has an error):

PL/SQL: SQL Statement ignored

PL/SQL: ORA-00920: invalid relational operator

I have already tried changing these variables without success (these were the lines highlighted to change according to Oracle):

  • SYS_CONTEXT('USERENV', 'SESSION_USER') -> to simply USER
  • SYS_CONTEXT('USERENV', 'SESSIONID') -> SESSIONID
  • SYS_CONTEXT('USERENV', 'HOST') -> HOST

The other line was the final end if;.

Several AI engines mentioned to remove the case statement CASE WHEN INSERTING THEN 'I' WHEN UPDATING THEN 'U' ELSE 'D' END, and place this logic in the code before the entire insert statement, without any success either.

I think something structural is wrong, but I can't find it.

Update: I changed the insert statement to:

  INSERT INTO audit_log(change_txid, scn, change_ts, db_user, session_id, client_host, module_name,
                        schema_name, table_name, op_type, pk_json, changed_columns)
  VALUES (NULL, NULL, SYSDATE, USER, NULL, NULL,NULL, 'DBNAME','CLIENTTIME', 'I', v_pk_json, v_changed);

And now the trigger compiled without errors.

2 Answers 2

6
  • Don't manually generate JSON. If one of the values contains a character that should be escaped then your manually generated JSON is not escaping it and will be invalid. Use one of the database's JSON data types (either in SQL or PL/SQL).
  • You cannot use INSERTING or DELETING in the SQL scope. You need to move the generation of the op_type value out of the SQL statement and into the PL/SQL.
  • ORA_ROWSCN is a pseudo-column and is not valid in an INSERT statement (and you probably don't want to capture it in the table). Just use SYSTIMESTAMP to capture the time of the change.
CREATE OR REPLACE TRIGGER ClientTime
  AFTER INSERT OR UPDATE OR DELETE ON ClientTime
  FOR EACH ROW
DECLARE
  v_pk_json  JSON_OBJECT_T := JSON_OBJECT_T();
  v_changed  JSON_OBJECT_T := JSON_OBJECT_T();
  v_type     VARCHAR2(1);
  v_pk_str   CLOB;
  v_ch_str   CLOB;
BEGIN
  IF INSERTING THEN
    v_type := 'I';
    v_pk_json.put('id', TO_CHAR(:NEW.ID));
    v_changed.put('id', TO_CHAR(:NEW.ID));
    v_changed.put('client', TO_CHAR(:NEW.CLIENT));
    v_changed.put('timestamp', TO_CHAR(:NEW.DATETIME, 'YYYY-MM-DD HH24:MI:SS'));
  ELSIF DELETING THEN
    v_type := 'D';
    v_pk_json.put('id', TO_CHAR(:OLD.ID));
    v_changed.put('id', TO_CHAR(:OLD.ID));
    v_changed.put('client', TO_CHAR(:OLD.CLIENT));
    v_changed.put('timestamp', TO_CHAR(:OLD.DATETIME, 'YYYY-MM-DD HH24:MI:SS'));
  ELSE -- UPDATE: only changed columns
    v_type := 'U';
    v_pk_json.put('id', TO_CHAR(:NEW.ID));

    DECLARE
      v_old_part JSON_OBJECT_T := JSON_OBJECT_T();
      v_new_part JSON_OBJECT_T := JSON_OBJECT_T();
    BEGIN
      IF (:OLD.CLIENT IS NULL AND :NEW.CLIENT IS NOT NULL)
      OR (:OLD.CLIENT IS NOT NULL AND :NEW.CLIENT IS  NULL)
      OR (:OLD.CLIENT <> :NEW.CLIENT)
      THEN
        v_old_part.put('client', TO_CHAR(:OLD.CLIENT));
        v_new_part.put('client', TO_CHAR(:NEW.CLIENT));
      END IF;

      IF (:OLD.DATETIME IS NULL AND :NEW.DATETIME IS NOT NULL)
      OR (:OLD.DATETIME IS NOT NULL AND :NEW.DATETIME IS  NULL)
      OR (:OLD.DATETIME <> :NEW.DATETIME)
      THEN
        v_old_part.put('timestamp', TO_CHAR(:OLD.DATETIME, 'YYYY-MM-DD HH24:MI:SS'));
        v_new_part.put('timestamp', TO_CHAR(:NEW.DATETIME, 'YYYY-MM-DD HH24:MI:SS'));
      END IF;

      v_changed.put('old', v_old_part);
      v_changed.put('new', v_new_part);
    END;
  END IF;

  v_pk_str := v_pk_json.to_string();
  v_ch_str := v_changed.to_string();

  -- insert into audit_log
  INSERT INTO audit_log(
    change_txid,
    change_ts,
    db_user,
    session_id,
    client_host,
    module_name,
    schema_name,
    table_name,
    op_type,
    pk_json,
    changed_columns
  ) VALUES (
    DBMS_TRANSACTION.LOCAL_TRANSACTION_ID,
    SYSTIMESTAMP,
    SYS_CONTEXT('USERENV','SESSION_USER'),
    SYS_CONTEXT('USERENV','SESSIONID'),
    SYS_CONTEXT('USERENV','HOST'),
    SYS_CONTEXT('USERENV','MODULE'),
    'DBNAME',
    'CLIENTTIME',
    v_type,
    v_pk_str,
    v_ch_str
  );
END;
/

Given the tables (note, the scn column has been removed):

CREATE TABLE clienttime (id, client, datetime) AS
SELECT 1, USER, SYSDATE FROM DUAL WHERE 1 = 0;

CREATE TABLE audit_log(
    change_txid,
    change_ts,
    db_user,
    session_id,
    client_host,
    module_name,
    schema_name,
    table_name,
    op_type,
    pk_json,
    changed_columns
  ) AS
SELECT DBMS_TRANSACTION.LOCAL_TRANSACTION_ID,
    SYSTIMESTAMP,
    SYS_CONTEXT('USERENV','SESSION_USER'),
    SYS_CONTEXT('USERENV','SESSIONID'),
    SYS_CONTEXT('USERENV','HOST'),
    SYS_CONTEXT('USERENV','MODULE'),
    'DBNAME',
    'CLIENTTIME',
    'I',
    EMPTY_CLOB(),
    EMPTY_CLOB()
FROM DUAL WHERE 1 = 0

Then if you make the changes:

INSERT INTO clienttime VALUES (1, 'ABC', DATE '1900-01-01');
INSERT INTO clienttime VALUES (2, '"DEF"', SYSDATE);
UPDATE clienttime SET datetime = SYSDATE WHERE id = 1;
COMMIT;
UPDATE clienttime SET datetime = datetime, client = client WHERE id = 2;
DELETE FROM clienttime;

Then the audit-log contains:

CHANGE_TXID CHANGE_TS DB_USER SESSION_ID CLIENT_HOST MODULE_NAME SCHEMA_NAME TABLE_NAME OP_TYPE PK_JSON CHANGED_COLUMNS
3.20.58764 2025-10-15 09:32:00.476190 +01:00 FIDDLE_OVXMZVVUYRAMNALWPHGV 3651642 fiddle-oracle21xe.localdomain php-fpm: pool [email protected] (TNS V1-V3) DBNAME CLIENTTIME I {"id":"1"} {"id":"1","client":"ABC","timestamp":"1900-01-01 00:00:00"}
3.20.58764 2025-10-15 09:32:00.482235 +01:00 FIDDLE_OVXMZVVUYRAMNALWPHGV 3651642 fiddle-oracle21xe.localdomain php-fpm: pool [email protected] (TNS V1-V3) DBNAME CLIENTTIME I {"id":"2"} {"id":"2","client":"\"DEF\"","timestamp":"2025-10-15 09:32:00"}
3.20.58764 2025-10-15 09:32:00.494862 +01:00 FIDDLE_OVXMZVVUYRAMNALWPHGV 3651642 fiddle-oracle21xe.localdomain php-fpm: pool [email protected] (TNS V1-V3) DBNAME CLIENTTIME U {"id":"1"} {"old":{"timestamp":"1900-01-01 00:00:00"},"new":{"timestamp":"2025-10-15 09:32:00"}}
1.13.59346 2025-10-15 09:32:00.500858 +01:00 FIDDLE_OVXMZVVUYRAMNALWPHGV 3651642 fiddle-oracle21xe.localdomain php-fpm: pool [email protected] (TNS V1-V3) DBNAME CLIENTTIME U {"id":"2"} {"old":{},"new":{}}
1.13.59346 2025-10-15 09:32:00.507250 +01:00 FIDDLE_OVXMZVVUYRAMNALWPHGV 3651642 fiddle-oracle21xe.localdomain php-fpm: pool [email protected] (TNS V1-V3) DBNAME CLIENTTIME D {"id":"1"} {"id":"1","client":"ABC","timestamp":"2025-10-15 09:32:00"}
1.13.59346 2025-10-15 09:32:00.507845 +01:00 FIDDLE_OVXMZVVUYRAMNALWPHGV 3651642 fiddle-oracle21xe.localdomain php-fpm: pool [email protected] (TNS V1-V3) DBNAME CLIENTTIME D {"id":"2"} {"id":"2","client":"\"DEF\"","timestamp":"2025-10-15 09:32:00"}
  • Note: The transaction ID changes after the COMMIT.
  • Note 2: When the client is "DEF" then the JSON value is properly escaped "\"DEF\"".

fiddle

Sign up to request clarification or add additional context in comments.

Comments

2
  END IF
  -- insert into audit_log

Missing ;

Also you can't use ORA_ROWSCN like this. That's a pseudo-column like rowid.

1 Comment

I think in copying the code the ; went fishing. The ORA_ROWSCN, how can I fix that/ replace it with something else?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.