PL/SQL STORED PROCEDURE for DATA Insert and Capture activity in the Log table:

Simple stored procedure to insert data into the EMP table in SCOTT schema and each successful insert will be logged into the log table(scott.procedure_logtable) and the data in the log table  records will be purged if the Create_timestamp of Log table is 180 days old by scheduling the Purge Procedure "Purge_scott_logtable"  with the schedular Job "Run_Purge_scott_logtable_job"

Also, the log table will capture the user info on who changed data and from which machine the SQL was executed.

LOG TABLE:

SQL>

Create table scott.procedure_logtable(

Activity Varchar2(40),

Created_by Varchar(10),

Create_timestamp Date,

From_machine Varchar(50)

)

tablespace users;

EMP TABLE:

Name     Null?    Type         

-------- -------- ------------ 

EMPNO    NOT NULL NUMBER(4)    

ENAME             VARCHAR2(10) 

JOB               VARCHAR2(9)  

MGR               NUMBER(4)    

HIREDATE          DATE         

SAL               NUMBER(7,2)  

COMM              NUMBER(7,2)  

DEPTNO            NUMBER(2)    

Actual Stored Procedure:

This Procedure will check if the Employee that is being inserted already exists in the EMP table and if Employee already exists then the message "'Employee 1234 already existed in the EMP table" will be displayed.

Otherwise, a new employee record will be inserted into the EMP table and log table.

SQL>

Create or REPLACE procedure InsertEmployee

(V_empno IN NUMBER, V_Ename IN Varchar2, V_Job IN VARCHAR2, V_MGR IN NUMBER, V_Hiredate IN DATE,  V_SAL IN NUMBER, V_COMM IN NUMBER, V_DEPTNO IN  NUMBER)

IS

user_name Varchar2(10);

Machine Varchar2(50);

LLogin Varchar2(40);

emp_count number:=1;

V_Activity varchar(60);

BEGIN

dbms_output.enable;

select count(1) into  emp_count from emp where empno=V_empno;

select user , sys_context('USERENV','HOST'), to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') into User_Name ,Machine, LLogin  from dual;

    IF (emp_count=1)  then

    dbms_output.put_line('Employee '||V_empno||' already existed in      the EMP table');

   

    ELSE

   

    IF (emp_count=0) then

   

    dbms_output.put_line('Inserting New Employee '||V_empno||' ato      EMP table');

    Insert into emp values(v_empno, V_Ename, V_Job, V_MGR, V_Hiredate, V_SAL,        V_COMM, V_DEPTNO);

   

    Dbms_output.put_line('Updating the log table');

    Insert into scott.procedure_logtable

    values(concat('Inserting New employee ',v_empno),             user_name,to_date(LLogin, 'yyyy-mm-dd hh24:mi:ss') ,Machine);

   

    Commit;

    END IF;

    END IF;

END;

/

Purge Stored Procedure:

Stored Procedure "Purge_scott_logtable" will delete the records in the log table "scott.procedure_logtable" based on the create_timestamp column 

SQL>

create or replace procedure Purge_scott_logtable

(V_days IN number)

IS

Begin

Delete from scott.procedure_logtable where create_timestamp < sysdate - V_days;

Commit;

END;

/

Purge Log Table Scheduler Job:

Scheduler Job "Run_Purge_scott_logtable_job" will invoke the Purge Procedure and pass the 180 as a parameter.

SQL>

BEGIN

  DBMS_SCHEDULER.create_job (

    job_name        => 'Run_Purge_scott_logtable_job',

    job_type        => 'PLSQL_BLOCK',

    job_action      => 'BEGIN scott.Purge_scott_logtable(180); END;',

    start_date      => SYSTIMESTAMP,

    repeat_interval => 'freq=hourly; byhour=23;',

    enabled         => TRUE);

END;

/

To Drop scheduler Job:

SQL>
BEGIN

  dbms_scheduler.drop_job(job_name => 'Run_Purge_scott_logtable_job');

END;

/