ORA-23607 in the Error Queue Using a DML Handler (Doc ID 553628.1)

Last updated on JANUARY 13, 2009

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.1.0.6
This problem can occur on any platform.

Symptoms

Creating a DML Handler where a column value is transformed using various logic and then applied using the following code:

CREATE OR REPLACE PROCEDURE lagtab_dml_handler(in_any IN SYS.ANYDATA)
IS

lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
object_owner VARCHAR2(30);
object_name VARCHAR2(40);
dmlcommand VARCHAR2(10);
row_dbname SYS.ANYDATA;
row_updatetime SYS.ANYDATA;
row_lag SYS.ANYDATA;
v_dbname varchar2(32);
v_updatetime date;
v_lag number;
remqry varchar2(132);
remdate date;
calclag number;


BEGIN
-- Access the LCR
rc := in_any.GETOBJECT(lcr);
object_owner := lcr.GET_OBJECT_OWNER();
object_name := lcr.GET_OBJECT_NAME();
dmlcommand := lcr.GET_COMMAND_TYPE();

IF object_owner = 'EODB_A' and
object_name = 'LAGTAB' and
dmlcommand = 'UPDATE' THEN

-- Get the dbname field from the record inserted
-- and current timestamp from remote
row_dbname := lcr.GET_VALUE('NEW','DBNAME');
row_updatetime := lcr.GET_VALUE('NEW','UPDATETIME');
row_lag := lcr.GET_VALUE('NEW','LAG');
rc := row_dbname.getvarchar2(v_dbname);
rc := row_updatetime.getdate(v_updatetime);
-- build a dynamic query
remqry := 'select localdate@'||v_dbname|| ' from dual';
execute immediate remqry into remdate;
calclag := (remdate-v_updatetime)*86400;
lcr.SET_VALUE('NEW','LAG',(sys.anydata.convertnumber(calclag)));
lcr.EXECUTE(TRUE);
END IF;
END;
/

This returns the following error: Ora-23607 : invalid column "LAG" in the error queue.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms