Dynamically accessing field items

[hs92]

I am trying to dynamically access the data in ld and :new fields without having to use the literal ld.field and :new.field.

The trigger below shows 2 ways of calling the p_addnote procedure. The first, now commented out, works by making a direct call of p_addnote with the literal data.
This works. In the second call I have called a second procedure that will dynamically create the call to p_addnote. This doesn’t work as I can’t access the :new or ld data.

Is it possible to dynamically get the data that is in the :new and ld field?


CREATE TRIGGER tr_update
BEFORE UPDATE
ON accounts
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
v_ntype users.notetype%TYPE;
Begin
SELECT notetype
INTO v_ntype
FROM users
WHERE code = lower(user);

IF :new.tb_company != ld.tb_company THEN
/*
p_addnoteold.tb_accountid,'AUD',
'Company ''' || substrold.tb_company,1,29) ||''' changed by ' || lower(USER) || '.',
v_ntype,0);
p_addnotenew.tb_accountid,'AUD',
'The new Company is ''' || substrnew.company,1,29) ||'''' ,
v_ntype,0);

*/
p_updnote('tb_accountid','tb_company','AUD','Company',v_ntype);
END IF;
END tr_update;



CREATE OR REPLACE PROCEDURE p_updnote
(
p_accountid unotes_bcs.not_accountid%TYPE,
p_field VARCHAR2,
p_code unotes_bcs.not_notecode%TYPE,
p_text unotes_bcs.not_text%TYPE,
p_user unotes_bcs.not_user%TYPE
)
IS
v_newfield VARCHAR2(20);
v_oldfield VARCHAR2(20);
BEGIN
v_newfield := ':new.' || p_field;
v_oldfield := 'ld.' || p_field;
p_addnote(p_accountid,p_code,p_text || '''' || v_oldfield || ''' changed by ' || lower(user) || '.',p_user);
p_addnote(p_accountid,p_code,'The new ' || p_text || 'is ''' || v_newfield || '''',p_user);
END p_updnote ;




PROCEDURE P_addnote
(
p_accountid unotes_bcs.not_accountid%TYPE,
p_code unotes_bcs.not_notecode%TYPE,
p_text unotes_bcs.not_text%TYPE,
p_user1 unotes_bcs.not_user%TYPE
)
IS
BEGIN
INSERT INTO tb_audit
(
not_accountid, not_dateofca, not_timeofca, not_text,
not_notecode, not_user, update_not, uptime_not, locked_not
)
VALUES
(
p_accountid, SYSDATE, p_text, p_code, p_user1
);
END;