Can Any one help me on this.
Thanks in advance
Naren
CREATE OR REPLACE PACKAGE DatabaseUtilities AS
function TriggerReferenceToRowType(prefix VARCHAR2,tableName VARCHAR2) RETURN VARCHAR2;
END DatabaseUtilities;
/
CREATE OR REPLACE PACKAGE BODY DatabaseUtilities
AS
FUNCTION table2rowtype ( prefix IN VARCHAR2, tableName IN VARCHAR2) RETURN VARCHAR2
IS
CURSOR v_cursor IS
SELECT column_name FROM user_tab_columns
WHERE TABLE_NAME = UPPER(tableName)
ORDER BY column_id;
v_sql varchar2(2000);
vv_sql varchar2(2000);
BEGIN
FOR v_rec IN v_cursor
LOOP
IF v_sql iS NOT NULL
THEN
v_sql := v_sql || ',';
vv_sql:= vv_sql || ',';
END IF;
v_sql := v_sql || prefix || v_rec.column_name;
vv_sql := vv_sql || v_rec.column_name;
end LOOP;
-- v_sql := 'SELECT ' || v_sql ; --|| ' FROM DUAL';
v_sql := v_sql ;
v_sql := 'insert into '||tableName ||' ('||vv_sql ||') values (' || v_sql || ')';
return v_sql;
END;
/* ----------------------------------------------------------------------------*/
FUNCTION TriggerReferenceToRowType (prefix VARCHAR2,tableName IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN table2rowtype(prefix, tableName);
END;
END DatabaseUtilities;
/
-----------------TRIGEGR
CREATE OR REPLACE TRIGGER TableToArchive_ArchiveTrigger
BEFORE INSERT or UPDATE or DELETE ON DEPT
REFERENCING OLD as old NEW as new
FOR EACH ROW
DECLARE
archiveRec DEPT%ROWTYPE ;
v varchar2(2000);
BEGIN
IF inserting OR updating or Deleting THEN
-- EXECUTE IMMEDIATE DatabaseUtilities.TriggerReferenceToRowType(':new.','DEPT') INTO archiveRec;
select :new.deptno,:new.dname,:new.loc into archiveRec from dual;
v:= DatabaseUtilities.TriggerReferenceToRowType(':new.','DEPT') ;
-- dbms_output.put_line(v||' '||archiveRec.deptno||','||archiveRec.dname||','||archiveRec.loc||');');
-- dbms_output.put_line(v);
-- v:= v||' into '||archiveRec||' from dual';
--v:= v||' into archiveRec from dual';
dbms_output.put_line(v);
-- execute immediate 'select '|| v || 'into '|| archiveRec ||' from dual';
-- 'select '||v || into archiveRec from dual;
-- execute immediate v;
-- dbms_output.put_line('after '||archiveRec.deptno);
ELSE
v:= DatabaseUtilities.TriggerReferenceToRowType(':old.','DEPT') ;
-- EXECUTE IMMEDIATE DatabaseUtilities.TriggerReferenceToRowType(':old.','DEPT') INTO archiveRec;
END IF;
-- call to package/procedure to process archiving: archive(archiveRec) ;
END ;