SET SERVEROUTPUT ON LINESIZE 10000

CREATE OR REPLACE TRIGGER LDAP_DIP

AFTER DELETE ON OID_SYNC
FOR EACH ROW

DECLARE 
 retval   PLS_INTEGER; 
 dip_session  DBMS_LDAP.session;
 logfile_handle  UTL_FILE.FILE_TYPE;
 user_dn   VARCHAR2(256);
 ldap_host  VARCHAR2(256);
 ldap_port  VARCHAR2(256);
 ldap_user  VARCHAR2(256);
 ldap_passwd  VARCHAR2(256);
 ldap_base  VARCHAR2(256);
 ldap_log_dir VARCHAR2(256);
 audit_log VARCHAR2(256);

BEGIN
 retval       := -1;
-- CHANGE THE FOLLOWING VARIABLES TO MATCH YOUR ENVIRONMENT
 ldap_host     := 'myhost.acme.com';
 ldap_port     := '3060';
 ldap_user     := 'cn=orcladmin';
 ldap_passwd   := 'welcome1';
 ldap_base     := 'cn=users,dc=us,dc=oracle,dc=com';
 ldap_log_dir  := '/u01/app/oracle/product/asdb/ldap/odi/log';
 audit_log     := 'DBImport.aud';

 DBMS_LDAP.USE_EXCEPTION := TRUE;

 logfile_handle := UTL_FILE.FOPEN (ldap_log_dir,audit_log,'A');
 dip_session := DBMS_LDAP.init(ldap_host,ldap_port);
 retval := DBMS_LDAP.simple_bind_s(dip_session,ldap_user,ldap_passwd);

 IF DELETING THEN
   user_dn := 'cn=' || :old.USER_NAME || ', ' || ldap_base ;
   retval := DBMS_LDAP.delete_s(dip_session,user_dn);
   UTL_FILE.put_line (logfile_handle, '**DB Trigger** : Success : X : ' || user_dn || ' Deleted'); 
 END IF;

 retval := DBMS_LDAP.unbind_s(dip_session);
 UTL_FILE.FCLOSE (logfile_handle);

 EXCEPTION
   WHEN UTL_FILE.INVALID_PATH THEN        
     DBMS_OUTPUT.PUT_LINE ('invalid_path'); 
     RAISE;   
   WHEN UTL_FILE.INVALID_MODE THEN        
     DBMS_OUTPUT.PUT_LINE ('invalid_mode'); 
     RAISE;   
   WHEN UTL_FILE.INVALID_FILEHANDLE THEN        
     DBMS_OUTPUT.PUT_LINE ('invalid_filehandle'); 
     RAISE;    
   WHEN UTL_FILE.INVALID_OPERATION THEN        
     DBMS_OUTPUT.PUT_LINE ('invalid_operation'); 
     RAISE;    
   WHEN UTL_FILE.READ_ERROR THEN         
     DBMS_OUTPUT.PUT_LINE ('read_error'); 
     RAISE;    
   WHEN UTL_FILE.WRITE_ERROR THEN       
     DBMS_OUTPUT.PUT_LINE ('write_error'); 
     RAISE;    
   WHEN UTL_FILE.INTERNAL_ERROR THEN       
     DBMS_OUTPUT.PUT_LINE ('internal_error'); 
   RAISE;
  WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE(' Error code    : ' || TO_CHAR(SQLCODE));
   DBMS_OUTPUT.PUT_LINE(' Error Message : ' || SQLERRM);
   DBMS_OUTPUT.PUT_LINE(' Exception encountered .. exiting');
END;
/

