-- PACKAGE FOR INSERT SCRIPT 
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 ;
/

insert into dept(deptno,dname,loc) values (70,'PERSONNEL','DALLAS')