SET SERVEROUTPUT ON; CREATE OR REPLACE TYPE cq_ib_trigger_keys_type IS OBJECT ( colName VarChar2(50), colValue VarChar2(4000) ); / CREATE OR REPLACE package cq_ib as --valsRec cq_ib_trigger_keys_type; Procedure GenerateMessageXml(refDataCursor IN cq_ib_trigger_keys_type); end CQ_IB; / CREATE OR REPLACE package body cq_ib IS procedure GenerateMessageXml( refDataCursor IN cq_ib_trigger_keys_type ) IS test VarChar2(400); testObj cq_ib_trigger_keys_type := refDataCursor; BEGIN dbms_output.put_line('Successfully deleted ' ); End GenerateMessageXml; End CQ_IB; / --testing tables DROP TABLE ORDERS; CREATE TABLE ORDERS ( order_id number(5), name varchar2(20), description varchar2(20) ); CREATE OR REPLACE TRIGGER ORDERS_TRIGGER BEFORE DELETE OR INSERT OR UPDATE ON ORDERS FOR EACH ROW DECLARE --array for valsRec cq_ib_trigger_keys_type; CURSOR cur IS SELECT column_name FROM all_tab_columns WHERE table_name = 'ORDERS'; BEGIN FOR row IN cur LOOP --INFO: make sure you work out how to loop the col names and make more generic valsRec.colName := row.column_name; valsRec.colValue := :new.NAME; dbms_output.put_line('the col name is : ' || valsRec.colName); dbms_output.put_line('the col VAL is : ' || valsRec.colValue); --dbms_output.put_line('-------first val : ' || :new.NAME(1) ); --my_courses := CourseList('Econ 2010', 'Acct 3401', 'Mgmt 3100'); --cq_ib.GenerateMessageXml(valsRecType); /* dbms_output.put_line('the col name is : ' || row.column_name); dbms_output.put_line('the col name is : ' || row.column_name); IF INSERTING THEN dbms_output.put_line('INSERTING: ' || 'NEW-' || :NEW.ORDER_ID || ':' || 'OLD-' || :OLD.ORDER_ID ); ELSIF UPDATING THEN dbms_output.put_line('UPDATING: ' || 'NEW-' || :NEW.NAME || ':' || 'OLD-' || :OLD.NAME); ELSIF DELETING THEN dbms_output.put_line('DELETING: ' || 'NEW-' || :NEW.DESCRIPTION || ':' || 'OLD-' || :OLD.DESCRIPTION); END IF; */ dbms_output.put_line('--------------------------------------------------------------------------'); END LOOP; END; / INSERT INTO ORDERS VALUES (1, 'donkeyman', 'donkeys'); INSERT INTO ORDERS VALUES (2, 'donkeysoft', 'brown'); INSERT INTO ORDERS VALUES (3, 'donkeyhat', 'white'); INSERT INTO ORDERS VALUES (4, 'donkeyleg', 'yellow'); INSERT INTO ORDERS VALUES (5, 'donkeyears', 'purple'); UPDATE ORDERS SET NAME = 'UPDATING' WHERE ORDER_ID = 1; UPDATE ORDERS SET NAME = 'UPDATING' WHERE ORDER_ID = 2; DELETE FROM ORDERS WHERE ORDER_ID IN (4,5); /