BEGIN
 DBMS_FGA.ADD_POLICY (
  object_schema      =>  'HR',
  object_name        =>  'EMPLOYEES',
  policy_name        =>  'CHK_HR_EMP',
  audit_column       =>  'SALARY', 
  handler_schema     =>  'SYSADMIN_FGA',
  handler_module     =>  'EMAIL_ALERT(object_schema, object_name )',
  enable             =>   TRUE,
  statement_types    =>  'SELECT, UPDATE',
  audit_trail        =>   DBMS_FGA.DB + DBMS_FGA.EXTENDED); 
END;
/




create or replace PROCEDURE test_email_alert (sch varchar2, tab varchar2)
AS
msg varchar2(20000) := sch||'.'||tab||' table violation. The time is: ';
BEGIN
  msg := msg||to_char(SYSDATE, 'Day DD MON, YYYY HH24:MI:SS');
UTL_MAIL.SEND (
    sender      => 'youremail@example.com',
    recipients  => ''youremail@example.com',
    subject     => 'Table modification on '||sch||'.'||tab,
    message     => msg);
END test_email_alert;

create or replace 
function get_emailer
       ( sch VARCHAR2 ,
         tab VARCHAR2 
         )
     
 return VARCHAR2 is 
 
 emailer_sql_stmt varchar2(1000);
 
   BEGIN

emailer_sql_stmt :=

 'CREATE OR REPLACE PROCEDURE audit_email_alert (sch varchar2, tab varchar2, pol varchar2)
AS
msg varchar2(20000) := ''' || sch || '.' || tab || ' table violation. The time is: '';
BEGIN
msg := msg||to_char(SYSDATE, ''Day DD MON, YYYY HH24:MI:SS'');
SYS.UTL_MAIL.SEND (
sender => ''john.okrasa@alithya.com'',
recipients => ''john.okrasa@alithya.com'',
subject => ''Table modification on '||sch||'.'||tab||''',
message => msg);
END audit_email_alert;';


--DBMS_OUTPUT.PUT_LINE('emailer_sql_stmt = :' || emailer_sql_stmt);
EXECUTE IMMEDIATE emailer_sql_stmt;

  EXCEPTION 
            WHEN OTHERS 
            THEN dbms_output.put_line('get_emailer error code: '||SQLCODE); 

RETURN 'audit_email_alert';
END;