spool syncwithoutgroups.log;
------------------------------------------------------------------------
-- 
-- FILE
-- syncwithoutgroups.sql :	BI User Community Sample Script
--								          to synchronize BI Catalog with users
--								          in OID 
--
-- DESCRIPTION
--
--      This SQL file contains the PL/SQL code required to import incremental
--      users (not belonging to groups) from Oracle Internet Directory 
--	  	(LDAP server) to BI Catalog 
--
--      This script needs some settings, please look in
--      the customizable settings section after first begin statement
--
--  NOTES
--  Make sure you have installed DBMS_LDAP package and the schema supplied with the sample
--	The output from this script will be recorded in syncwithoutc_groups.log
--	Optional group name may be supplied
------------------------------------------------------------------------
set serveroutput on size 100000
set define on

DECLARE 
	c_ldap_host  			      VARCHAR2(256);
	c_ldap_port  			      VARCHAR2(256);
	c_ldap_user  			      VARCHAR2(256);
	c_ldap_passwd  			    VARCHAR2(256);
	c_ldap_base  			      VARCHAR2(256);
	c_sync_filter 			    VARCHAR2(128);
	c_search_scope				  PLS_INTEGER := DBMS_LDAP.SCOPE_SUBTREE;
	c_permission			      PLS_INTEGER := 10;
	c_user_trace 			      BOOLEAN := true;
	c_groups     			      DBMS_LDAP.string_collection;
  c_username_attribute 	  VARCHAR2(100);
  c_common_container_dn   VARCHAR2(256);
	-- program variables
	v_retval1       		    PLS_INTEGER;
	v_session   			      DBMS_LDAP.session;
	v_attrs     			      DBMS_LDAP.string_collection;
	v_message   			      DBMS_LDAP.message;
	v_entry     			      DBMS_LDAP.message;
	v_entry_index  			    PLS_INTEGER;
	v_attr_name 			      VARCHAR2(256);
	v_ber_elmt  			      DBMS_LDAP.ber_element;
	i          				      PLS_INTEGER;
	v_vals 					        DBMS_LDAP.STRING_COLLECTION ;
	v_version         		  VARCHAR2(1000) ;
	v_comp_version    		  VARCHAR2(1000) ;
	v_userlist 				      List := List();
	v_grplist 				      List := List();
	v_retval2 				      VARCHAR2(256);
	v_extended  			      BOOLEAN := false;
	v_currentTime			      VARCHAR2(56);
  v_nicknameattribute     VARCHAR2(100);
BEGIN

--- CUSTOMIZABLE SETTINGS BEGIN ----
	c_ldap_host  	        := 'ykosuru-pc.us.oracle.com';
	c_ldap_port  	        := '389';
	c_ldap_user  	        := 'cn=orcladmin';
	c_ldap_passwd	        := 'welcome';
	c_ldap_base  	        := 'cn=OracleContext';
	c_groups(1) 	        := 'world';
	c_search_scope        :=  DBMS_LDAP.SCOPE_SUBTREE; 
  c_user_trace 	        := true;
	c_permission 	        := 10; 
	c_sync_filter 	      := '(&(objectclass=inetOrgPerson)(|(createtimestamp>=20020129000000)(modifytimestamp>=20020129000000)))';
  c_username_attribute  := null;
  c_common_container_dn := 'cn=Common,cn=Products,cn=OracleContext';
--- CUSTOMIZABLE SETTINGS END ----

  -- get the current time in UTC format -- 
	select to_char(sysdate,'YYYYMMDDHH24MISS') into v_currentTime from dual;
	dbms_output.put_line('Script Execution Time '|| v_currentTime);
  -- make sure that the RDBMS is not 8.x version
  dbms_utility.db_version(v_version, v_comp_version);
	dbms_output.put_line('Oracle Server Version '|| v_version||' detected ');
	if substr(v_version, 1, 2) = '8.' then
		dbms_output.put_line('Warning : In Oracle 8.x release, this script should not be run in MTS mode');
	end if;
	v_retval1         := -1;
	DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');
	DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Host ',25,' ') || ': ' || c_ldap_host);
	DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Port ',25,' ') || ': ' || c_ldap_port);
	DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Base ',25,' ') || ': ' || c_ldap_base);
	-- Choosing exceptions to be raised by DBMS_LDAP library.
	DBMS_LDAP.USE_EXCEPTION := TRUE;

  -- create a session with LDAP server
	-- we just need one handle to LDAP 
  v_session := DBMS_LDAP.init(c_ldap_host,c_ldap_port);
	-- bind to the directory
	v_retval1 := DBMS_LDAP.simple_bind_s(v_session, c_ldap_user, c_ldap_passwd);

  -- if username attribute has not been specified,  look for nick name attribute in LDAP server
  -- if user name attribute is specified, it takes precdence
	if c_username_attribute is null then
   -- get orcl common nick name attribute
    v_nicknameattribute := LDAP_BICATALOG_SYNC_UTILS.getnicknameattribute(v_session,c_common_container_dn,c_user_trace);
    if v_nicknameattribute is null then
      if c_common_container_dn is null then
       -- if username attr is not specified, then common container dn must be specified
        dbms_output.put_line('TRACE: Either c_username_attribute must be set or c_common_container_dn must be set');
      else
        dbms_output.put_line('TRACE: orclcommonnicknameattribute value could not be identified');
        dbms_output.put_line('TRACE: Make sure common container and orclcommonnicknameattribute exists');
      end if;
      
      dbms_output.put_line('TRACE: Script terminating');
      goto exit_script;
    else
      c_username_attribute := v_nicknameattribute;
    end if;
	end if;
  
	-- construct v_grplist from C_GROUPS specified by the user
	if c_groups is not null and c_groups.count > 0 then
		FOR i in c_groups.FIRST..c_groups.LAST loop
			v_grplist.extend();
			v_grplist(i) := ListObject(c_groups(i),null,'f','f','f');
		end loop;
	end if;
    -- following is the attributes we want the search to return
	v_attrs(1) := c_username_attribute;
	v_attrs(2) := 'orclguid';
  -- search LDAP for entries matching the criteria
	v_retval1 := DBMS_LDAP.search_s(v_session, c_ldap_base,
							  c_search_scope,
							  c_sync_filter,
							  v_attrs,
							  0,
							  v_message);

	-- count the number of entries returned, each entry corresponds to an user
	v_retval1 := DBMS_LDAP.count_entries(v_session, v_message);
  if c_user_trace = true then 
	DBMS_OUTPUT.PUT_LINE(RPAD('Number of Entries Found ',25,' ') || ': '
												|| TO_CHAR(v_retval1));
  end if;
	DBMS_OUTPUT.PUT_LINE('===================================================');

	-- get the first entry
	v_entry := DBMS_LDAP.first_entry(v_session, v_message);
	v_entry_index := 1;
	-- Loop through each of the entries one by one
	while v_entry IS NOT NULL loop
		v_extended := false;
    -- retrieve the attributes
		v_attr_name := DBMS_LDAP.first_attribute(v_session,v_entry, v_ber_elmt);
		while v_attr_name IS NOT NULL loop
      -- retrieve the values associated with an attribute
			v_vals := DBMS_LDAP.get_values (v_session, v_entry,v_attr_name);
			if v_vals.COUNT > 0 then
				FOR i in v_vals.FIRST..v_vals.LAST loop
				if i = 0 then
					if v_extended = false then
						v_userlist.extend();
						v_userlist(v_userlist.count) := ListObject(null,null,'f','f','f');
						v_extended := true;
					end if;
          -- if it is the user name attribute, then consider that as username
					if v_attr_name = c_username_attribute then
						v_userlist(v_userlist.count).name := SUBSTR(v_vals(i),1,length(v_vals(i)));
          -- we need to ORCLGUID to keep track of entries imported from LDAP
					elsif v_attr_name = 'orclguid' then
						v_userlist(v_userlist.count).guid := SUBSTR(v_vals(i),1,length(v_vals(i)));
					end if;
				else
					DBMS_OUTPUT.PUT_LINE('WARNING : ignoring '||v_attr_name || ' : ' ||SUBSTR(v_vals(i),1,200));
				end if;
				end loop;
			end if;
			v_attr_name := DBMS_LDAP.next_attribute(v_session,v_entry,v_ber_elmt);
		end loop;  -- end attribute loop

	-- add to catalog once every 1000 users
	if mod(v_entry_index, 1000)  = 0 then
		-- flush every 1000 elements
		v_retval2 := LDAP_BICATALOG_SYNC_UTILS.addUsersToCatalog(v_userlist,v_grplist,c_permission,c_user_trace);
		v_userlist.delete;
	end if;
	v_entry := DBMS_LDAP.next_entry(v_session, v_entry);
	DBMS_OUTPUT.PUT_LINE('===================================================');
	v_entry_index := v_entry_index+1;
	end loop;

	-- flush any elements in the userlist
  -- the specified groups (if any) will be created, users will be created and 
  -- association between the group (if any) and the users will be made in the catalog
  -- privileges if requested will be granted to the user and group 
	v_retval2 := LDAP_BICATALOG_SYNC_UTILS.addUsersToCatalog(v_userlist,v_grplist,c_permission,c_user_trace);
	v_userlist.delete;
	v_grplist.delete;

	-- free LDAP Message
	v_retval1 := DBMS_LDAP.msgfree(v_message);
	-- unbind from the directory
	v_retval1 := DBMS_LDAP.unbind_s(v_session);
	DBMS_OUTPUT.PUT_LINE('Directory operation Successful .. exiting');
	<<exit_script>>
	DBMS_OUTPUT.PUT_LINE('Script exiting');
	-- Handle Exceptions
EXCEPTION
	WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE('ERROR: code    : ' || TO_CHAR(SQLCODE));
	DBMS_OUTPUT.PUT_LINE('ERROR: Message : ' || SQLERRM);
END;
/


spool off;
set verify on
set trimspool on
set feedback on
set termout on
set define off

