oracle循环表字段报错的问题

Question

需求：我想下个触发器，在插入和更新时调用这个触发器，用一个循环去循环这个表的所有字段，判断这个字段有没发生变化，如果发生变化，将这个字段名，原来的值，新的值插入到另一张表
目前实现：

CREATE OR REPLACE TRIGGER TR_MEM_MEMBER_LOG
  AFTER UPDATE ON PRD_EXT_ZH
  FOR EACH ROW
DECLARE
  CURSOR CUR_MEM_MEMBER_LOG_DATA IS
    SELECT COLUMN_NAME
      FROM USER_TAB_COLS
     WHERE TABLE_NAME = 'PRD_EXT_ZH';
  V_COLUMN_NAME USER_TAB_COLS.COLUMN_NAME%TYPE;
  V_N_COLUMN_NAME_VALUE VARCHAR2(200);
  V_O_COLUMN_NAME_VALUE VARCHAR2(200);
BEGIN
    OPEN CUR_MEM_MEMBER_LOG_DATA;
    LOOP
      EXECUTE IMMEDIATE 'SELECT :NEW.'|| V_COLUMN_NAME||',:OLD.'||V_COLUMN_NAME||' FROM DUAL'
                   INTO V_N_COLUMN_NAME_VALUE,V_O_COLUMN_NAME_VALUE;
      IF V_N_COLUMN_NAME_VALUE <> V_O_COLUMN_NAME_VALUE THEN
       insert into change_pims(UP_PER_ID,TABLE_NAME,OLD_FIELD,NEW_FIELD,PRD_ID,FIELD_NAME)
        VALUES
          (:new.UP_PER_ID,
           'prd_ext_zh',
            ':OLD.' || V_COLUMN_NAME,
           ':NEW.' || V_COLUMN_NAME,
     :new.prd_id,
           V_COLUMN_NAME);
      END IF;
    END LOOP;
    CLOSE CUR_MEM_MEMBER_LOG_DATA;
END TR_MEM_MEMBER_LOG;

报错：
Caused by: ErrCode: 14101017
Message: SQL语法错误(Could not execute JDBC batch update)
at com.primeton.das.entity.impl.exception.DASExceptionHelper.convert(DASExceptionHelper.java:150)
at com.primeton.das.entity.impl.DASSessionImpl.updateEntity(DASSessionImpl.java:214)
at com.eos.foundation.database.DatabaseUtil.updateEntity(DatabaseUtil.java:282)
... 60 more
Caused by: com.primeton.das.entity.impl.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
at com.primeton.das.entity.impl.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at com.primeton.das.entity.impl.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at com.primeton.das.entity.impl.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:264)
at com.primeton.das.entity.impl.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)
at com.primeton.das.entity.impl.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:140)
at com.primeton.das.entity.impl.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
at com.primeton.das.entity.impl.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at com.primeton.das.entity.impl.hibernate.impl.SessionImpl.flush(SessionImpl.java:1021)
at com.primeton.das.entity.impl.DASSessionImpl.updateEntity(DASSessionImpl.java:211)
... 61 more
Caused by: java.sql.BatchUpdateException: ORA-00919: 无效函数
ORA-06512: 在 "PIM.TR_MEM_MEMBER_LOG", line 13
ORA-04088: 触发器 'PIM.TR_MEM_MEMBER_LOG' 执行过程中出错

Answer 1

https://zhidao.baidu.com/question/2271183163156574268.html