This appendix provides the contents of the following files, which were used to set up provisioning for Oracle BI using Directory Integration Platform (DIP): - Usapp.dat
- appriv.dat
- ldap_ntfy_30.sql
- ldap_ntfy_30.pks
- ldap_ntfy_30.pkb
This provisioning setup is described in the topic Integrating Oracle Internet Directory With Oracle Business Intelligence. NOTE: The contents of these files are samples only. Before using, review these files and modify based on your environment.
Reference:
$host = Hostname of the machine where OID and DIP are installed $port = 389 for non ssl connection $passwd = Password for superuser orcladmin $db_host = host name of the Oracle database server where the provisioning is to be done $connect = Oracle SID (directory database connect string, or the net service name in tnsnames.ora) $db_userid = db user $db_userpassword = password for db user ADD(*) = attributes you want provisioned through DIP when a new entity is added in OID. Use asterisk (*) to provision all attributes, or provide a list of required attributes. Contents of Uspapp.dat:
dn: cn=DUMMYAPP,cn=Products,cn=OracleContext
changetype: add
objectclass: orclcontainer
dn: orclApplicationCommonName=DummyappOnline,cn=DUMMYAPP,cn=Products,cn=OracleContext
changetype: add
objectclass: orclApplicationEntity
orclapplicationcommonname: DummyappOnline
orclappfullname: Oracle Dummyapp Online
userpassword: welcome123
description: This is a test Appliction instance
protocolInformation: Dummy Information
orclVersion: 1.0
orclaci: access to entry by group="cn=odisgroup,cn=DIPAdmins,cn=Directory Integration Platform,cn=Products,cn=Oraclecontext" (browse,pr
oxy) by group="cn=User Provisioning Admins,cn=Groups,cn=OracleContext" (add,delete,browse)
orclaci: access to attr=(*) by group="cn=User Provisioning Admins,cn=Groups,cn=OracleContext" (search,read,write,compare)
dn: cn=User Properties,orclApplicationCommonName=DummyappOnline,cn=DUMMYAPP,cn=Products,cn=OracleContext
changetype: add
objectclass: orclcontainer
Contents of appriv.dat:
dn: cn=OracleDASCreateUser,cn=Groups,cn=OracleContext
changetype: modify
add: uniquemember
uniquemember: orclApplicationCommonName=DummyappOnline,cn=DUMMYAPP,cn=Products,cn=OracleContext
dn: cn=OracleDASDeleteUser,cn=Groups,cn=OracleContext
changetype: modify
add: uniquemember
uniquemember: orclApplicationCommonName=DummyappOnline,cn=DUMMYAPP,cn=Products,cn=OracleContext
dn: cn=OracleDASEditUser,cn=Groups,cn=OracleContext
changetype: modify
add: uniquemember
uniquemember: orclApplicationCommonName=DummyappOnline,cn=DUMMYAPP,cn=Products,cn=OracleContext
dn: cn=OracleDASCreateGroup,cn=Groups,cn=OracleContext
changetype: modify
add: uniquemember
uniquemember: orclApplicationCommonName=DummyappOnline,cn=DUMMYAPP,cn=Products,cn=OracleContext
dn: cn=OracleDASDeleteGroup,cn=Groups,cn=OracleContext
changetype: modify
add: uniquemember
uniquemember: orclApplicationCommonName=DummyappOnline,cn=DUMMYAPP,cn=Products,cn=OracleContext
dn: cn=OracleDASEditGroup,cn=Groups,cn=OracleContext
changetype: modify
add: uniquemember
uniquemember: orclApplicationCommonName=DummyappOnline,cn=DUMMYAPP,cn=Products,cn=OracleContext
Contents of ldap_ntfy_30.sql:
Rem Rem $Header: ldap_ntfy_30.sql 28-mar-2005.01:29:44 sasrivas Exp $ Rem
Rem ldap_ntfy_30.sql
Rem Rem Copyright (c) 2004, 2005, Oracle. All rights reserved. Rem
Rem NAME Rem ldap_ntfy_30.sql - Provisioning -- App tables for out/in bound data.
Rem Rem DESCRIPTION Rem <short description of component this file declares/defines
Rem Rem NOTES Rem <other useful comments, qualifications, etc.
Rem Rem MODIFIED (MM/DD/YY) Rem sasrivas 03/28/05 - Merge from 101201 to Main Linux Rem sasrivas 02/17/05 - sasrivas_ocs_sync_tx2 Rem vasokkum 10/05/04 - Change ADT Names to have v3. Rem sasrivas 06/28/04 - sasrivas_ocsprovtest Rem sasrivas 06/23/04 - Moved the file from MAIN to OCS10.1.2 Rem snamudur 02/11/04 - snamudur_userprov1 Rem snamudur 02/11/04 - Created Rem
SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
DROP TABLE APP_TO_OID_EVENT_MASTER ;
DROP TABLE APP_TO_OID_EVENT_DETAILS ;
DROP TABLE APP_TO_OID_EVENT_STATUS;
DROP TABLE OID_TO_APP_RECEIVED_EVENTS ;
DROP TABLE OID_TO_APP_EVENTS_COUNT ;
DROP TABLE OID_PROV_PARAMS_STATUS ;
REM This is the Table For the Canned Data To be Propagated From APP to OID
REM Used only for INBOUND profiles
CREATE TABLE APP_TO_OID_EVENT_MASTER (
EVENT_ID NUMBER, -- Numeric Event Identifier
EVENT_TYPE VARCHAR2(32), -- Type of Event like SUBSCRIPTION_ADD
EVENT_SRC VARCHAR2(1024), -- Modifier's Name. Used only from OID
OBJECT_NAME VARCHAR2(1024), -- Used only in Events from OID
OBJECT_GUID VARCHAR2(32), -- Unique Global Identifier
OBJECT_DN VARCHAR2(1024), -- Used only in Events from OID
OBJECT_TYPE VARCHAR2(32), -- Type Of Object. App specific
PROFILE_ID VARCHAR2(256), -- Used only in Events from OID
DESCRIPTION VARCHAR2(256)) -- Description
/
CREATE UNIQUE INDEX APP_TO_OID_EVENT_MASTER_I ON
APP_TO_OID_EVENT_MASTER (EVENT_ID)
/
REM This is the Table For the Canned Data To be Propagated From APP to OID
REM This contains the event details.Used only for INBOUND profiles
CREATE TABLE APP_TO_OID_EVENT_DETAILS (
EVENT_ID NUMBER, -- Foreign Key
ATTR_NAME VARCHAR2(64),
ATTR_TYPE NUMBER,
ATTR_VALUE VARCHAR2(4000),
ATTR_MOD_OP NUMBER)
/
REM This is the Table used to put the STATUS events received from DIP
REM Used only for INBOUND profiles
CREATE TABLE APP_TO_OID_EVENT_STATUS (
EVENT_ID NUMBER,
EVENT_STATUS VARCHAR2(32),
EVENT_STATUS_MSG VARCHAR2(1024),
ORCLGUID VARCHAR(32))
/
REM This is the Table used to put the EVENTS received from DIP.
REM Used only for OUTBOUND profiles
CREATE TABLE OID_TO_APP_RECEIVED_EVENTS(
EVENT_TYPE VARCHAR2(32),
EVENT_ID NUMBER,
EVENT_SRC VARCHAR2(256),
EVENT_TIME VARCHAR2(32),
OBJECT_NAME VARCHAR2(256),
OBJECT_TYPE VARCHAR2(32),
OBJECT_GUID VARCHAR2(32),
OBJECT_DN VARCHAR2(256),
PROFILE_ID VARCHAR2(256),
ATTR_NAME VARCHAR2(64),
ATTR_VALUE VARCHAR2(256),
ATTR_VALUE_LEN VARCHAR2(32),
ATTR_MOD_OP VARCHAR2(32))
/
CREATE TABLE OID_TO_APP_EVENTS_COUNT(
ID NUMBER,
EVENT_COUNT NUMBER
)
/
CREATE TABLE OID_PROV_PARAMS_STATUS(
LAST_EVENT_PROCESSED_BY_OID NUMBER, --Used to keep track of INBOUND sync
LAST_EVENT_PROCESSED_BY_APP NUMBER, --Used to keep track of OUTBOUND sync
EVENT_BATCH_SIZE NUMBER, -- These packages uses this to simulate bulk
-- events for INBOUND profiles.Should match
-- the value in profile
ENCRYPTION_KEY VARCHAR2(32)) -- Used to encrypt/decrypt data.
-- Should match with what is in the profile.
/
INSERT INTO OID_PROV_PARAMS_STATUS VALUES (0,0,'1','ABCDDCBA')
/
DROP SEQUENCE PROV_TEST_DBG;
CREATE SEQUENCE PROV_TEST_DBG START WITH 1;
DROP TABLE OID_PROV_DEBUG_LOG
/
CREATE TABLE OID_PROV_DEBUG_LOG(
LOG_SEQ NUMBER,
LOG_MSG VARCHAR2(1024))
/
exit ;
Contents of ldap_ntfy_30.pks:
-- The PL/SQL Interface Specification for interface version 3.0
-- The OID 9.0.2 used the interface version # 1.1
-- The OID 9.0.4 used the interface version # 2.0
-- The OID 9.0.? (OCSr3) will use the interface version # 3.0
DROP TYPE LDAP_EVENT_LIST_V3;
DROP TYPE LDAP_EVENT_STATUS_LIST_V3;
DROP TYPE LDAP_EVENT_V3;
DROP TYPE LDAP_EVENT_STATUS_V3;
DROP TYPE LDAP_ATTR_LIST_V3;
DROP TYPE LDAP_ATTR_V3;
DROP TYPE LDAP_ATTR_VALUE_LIST_V3;
DROP TYPE LDAP_ATTR_VALUE_V3;
CREATE TYPE LDAP_ATTR_VALUE_V3 AS OBJECT (
attr_value VARCHAR2(4000),
attr_bvalue RAW(2048),
attr_value_len INTEGER
);
/
GRANT EXECUTE ON LDAP_ATTR_VALUE_V3 to public;
CREATE TYPE LDAP_ATTR_VALUE_LIST_V3 AS TABLE OF LDAP_ATTR_VALUE_V3;
/
GRANT EXECUTE ON LDAP_ATTR_VALUE_LIST_V3 to public;
CREATE TYPE LDAP_ATTR_V3 AS OBJECT (
attr_name VARCHAR2(256),
attr_type INTEGER,
attr_mod_op INTEGER,
attr_values LDAP_ATTR_VALUE_LIST_V3
);
/
GRANT EXECUTE ON LDAP_ATTR_V3 to public;
CREATE TYPE LDAP_ATTR_LIST_V3 AS TABLE OF LDAP_ATTR_V3;
/
GRANT EXECUTE ON LDAP_ATTR_LIST_V3 to public;
CREATE TYPE LDAP_EVENT_V3 AS OBJECT (
event_type VARCHAR2(32),
event_id VARCHAR2(32),
event_src VARCHAR2(1024),
event_time VARCHAR2(32),
object_name VARCHAR2(1024),
object_type VARCHAR2(32),
object_guid VARCHAR2(32),
object_dn VARCHAR2(1024),
profile_id VARCHAR2(1024),
attr_list LDAP_ATTR_LIST_V3 ) ;
/
GRANT EXECUTE ON LDAP_EVENT_V3 to public;
CREATE TYPE LDAP_EVENT_LIST_V3 AS TABLE OF LDAP_EVENT_V3;
/
GRANT EXECUTE ON LDAP_EVENT_LIST_V3 to public;
CREATE TYPE LDAP_EVENT_STATUS_V3 AS OBJECT (
event_id VARCHAR2(32),
event_status VARCHAR2(32),
event_status_msg VARCHAR2(2048),
orclguid VARCHAR(32)) ;
/
GRANT EXECUTE ON LDAP_EVENT_STATUS_V3 to public;
CREATE TYPE LDAP_EVENT_STATUS_LIST_V3 AS TABLE OF LDAP_EVENT_STATUS_V3;
/
GRANT EXECUTE ON LDAP_EVENT_STATUS_LIST_V3 to public;
-- A Test Package. The name of the package is configurable. The the procedure
-- definitions are interface spec. compliant.
CREATE OR REPLACE PACKAGE PRASA_LDAP_NTFY AS
-- The Event Types
ENTRY_ADD CONSTANT VARCHAR2(32) := 'ENTRY_ADD';
ENTRY_DELETE CONSTANT VARCHAR2(32) := 'ENTRY_DELETE';
ENTRY_MODIFY CONSTANT VARCHAR2(32) := 'ENTRY_MODIFY';
USER_ADD CONSTANT VARCHAR2(32) := 'USER_ADD';
USER_DELETE CONSTANT VARCHAR2(32) := 'USER_DELETE';
USER_MODIFY CONSTANT VARCHAR2(32) := 'USER_MODIFY';
IDENTITY_ADD CONSTANT VARCHAR2(32) := 'IDENTITY_ADD';
IDENTITY_DELETE CONSTANT VARCHAR2(32) := 'IDENTITY_DELETE';
IDENTITY_MODIFY CONSTANT VARCHAR2(32) := 'IDENTITY_MODIFY';
GROUP_ADD CONSTANT VARCHAR2(32) := 'GROUP_ADD';
GROUP_DELETE CONSTANT VARCHAR2(32) := 'GROUP_DELETE';
GROUP_MODIFY CONSTANT VARCHAR2(32) := 'GROUP_MODIFY';
SUBSCRIPTION_ADD CONSTANT VARCHAR2(32) := 'SUBSCRIPTION_ADD';
SUBSCRIPTION_DELETE CONSTANT VARCHAR2(32) := 'SUBSCRIPTION_DELETE';
SUBSCRIPTION_MODIFY CONSTANT VARCHAR2(32) := 'SUBSCRIPTION_MODIFY';
SUBSCRIBER_ADD CONSTANT VARCHAR2(32) := 'SUBSCRIBER_ADD';
SUBSCRIBER_DELETE CONSTANT VARCHAR2(32) := 'SUBSCRIBER_DELETE';
SUBSCRIBER_MODIFY CONSTANT VARCHAR2(32) := 'SUBSCRIBER_MODIFY';
-- The Attribute Modification Type
ATTR_TYPE_STRING CONSTANT NUMBER := 0;
ATTR_TYPE_BINARY CONSTANT NUMBER := 1;
ATTR_TYPE_ENCRYPTED_STRING CONSTANT NUMBER := 2;
ATTR_TYPE_DATE_STRING CONSTANT NUMBER := 3;
-- The Attribute Modification Type
MOD_ADD CONSTANT NUMBER := 0;
MOD_DELETE CONSTANT NUMBER := 1;
MOD_REPLACE CONSTANT NUMBER := 2;
-- The Event dispostions constants
EVENT_SUCCESS CONSTANT VARCHAR2(32) := 'EVENT_SUCCESS';
EVENT_ERROR_IGNORE CONSTANT VARCHAR2(32) := 'EVENT_ERROR_IGNORE';
EVENT_ERROR_RESEND CONSTANT VARCHAR2(32) := 'EVENT_ERROR_RESEND';
EVENT_ERROR_FATAL CONSTANT VARCHAR2(32) := 'EVENT_ERROR_FATAL';
-- The Actual Procedures
-- PUTOIDEVENTS : DIP Server invokes this API in the remote Database to
-- propagate a bunch of events to the application.
-- DIP server expects an event_status_list object in response as an OUT
-- parameter. If valid event status object is not sent back or it
-- indicates a RESEND, DIP server will keep resending from that event
-- indefnitely.
PROCEDURE PutOIDEvents (event_list IN LDAP_EVENT_LIST_V3,
event_status_list OUT LDAP_EVENT_STATUS_LIST_V3);
-- GETAPPEVENT : DIP Server invokes this API in the remote Database.
-- It is up to the appliction to respond with an event list
-- Once DIP gets the event , it processes the event and sends the status
-- back.
-- The return value indicates whether any more event is returned or not.
FUNCTION GetAppEvents (event_list OUT LDAP_EVENT_LIST_V3)
RETURN NUMBER;
-- Return CONSTANTS
EVENT_FOUND CONSTANT NUMBER := 0;
EVENT_NOT_FOUND CONSTANT NUMBER := 1403;
PROCEDURE PutAppEventStatus (event_status_list IN LDAP_EVENT_STATUS_LIST_V3);
FUNCTION Decrypt(inputStr IN VARCHAR2) RETURN VARCHAR2;
PROCEDURE LogTrace(inputStr IN VARCHAR2) ;
END PRASA_LDAP_NTFY;
/
exit ;
Contents of ldap_ntfy_30.pkb:
CREATE OR REPLACE PACKAGE BODY PRASA_LDAP_NTFY AS
sqlStatus NUMBER;
LastEventProcByOID NUMBER;
LastEventSentByAPP NUMBER;
-- ==========================================================================
PROCEDURE LogTrace(inputStr IN VARCHAR2)
IS
BEGIN
INSERT INTO OID_PROV_DEBUG_LOG VALUES (PROV_TEST_DBG.NEXTVAL,inputStr);
DBMS_OUTPUT.PUT_LINE(inputStr);
END ;
-- ==========================================================================
FUNCTION Encrypt(inputStr IN VARCHAR2)
RETURN VARCHAR2
IS
key VARCHAR2(8);
inputStrCopy VARCHAR2(128);
inputStrLen NUMBER;
paddingRqd NUMBER;
outputStr VARCHAR2(256);
BEGIN
SELECT ENCRYPTION_KEY INTO key FROM OID_PROV_PARAMS_STATUS;
inputStrLen := LENGTH(inputStr);
paddingRqd := 8 - (MOD(inputStrLen,8));
IF (paddingRqd = 0 OR paddingRqd = 8)
THEN
paddingRqd := 0;
inputStrCopy := inputStr;
ELSE
inputStrCopy := RPAD(inputStr,inputStrLen+paddingRqd);
END IF;
LogTrace('Padding :' || paddingRqd);
outputStr := RAWTOHEX(UTL_RAW.CAST_TO_RAW(DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(INPUT_STRING = inputS
LogTrace('Encrypted Value :(' || outputStr || ')' );
RETURN (outputStr);
END ;
-- ==========================================================================
FUNCTION Decrypt(inputStr IN VARCHAR2)
RETURN VARCHAR2
IS
key VARCHAR2(8);
outputStr VARCHAR2(256);
BEGIN
SELECT ENCRYPTION_KEY INTO key FROM OID_PROV_PARAMS_STATUS;
outputStr := DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(INPUT_STRING = UTL_RAW.CAST_TO_RAW(HEXTORAW(inputS
LogTrace('Decrypted Value :(' || outputStr || ')' );
RETURN (outputStr);
END ;
-- ==========================================================================
FUNCTION GetAppEvents (event_list OUT LDAP_EVENT_LIST_V3)
RETURN NUMBER
IS
attr_list LDAP_ATTR_LIST_V3;
attr_value_list LDAP_ATTR_VALUE_LIST_V3;
status NUMBER;
lstproc VARCHAR2(32);
evt_idx NUMBER := 0;
batchSize NUMBER := 0;
i NUMBER;
j NUMBER;
CURSOR c_EVTS(StartNum IN NUMBER) IS
SELECT
EVENT_ID,
EVENT_TYPE,
EVENT_SRC,
OBJECT_NAME,
OBJECT_TYPE,
OBJECT_GUID,
OBJECT_DN,
PROFILE_ID,
DESCRIPTION
FROM
APP_TO_OID_EVENT_MASTER
WHERE
EVENT_ID StartNum
ORDER BY EVENT_ID;
CURSOR c_EVT_DTLS (EVENTID IN NUMBER) IS
SELECT
ATTR_NAME,
ATTR_TYPE,
ATTR_VALUE,
ATTR_MOD_OP
FROM
APP_TO_OID_EVENT_DETAILS
WHERE
EVENT_ID = EVENTID
ORDER BY ATTR_NAME,ATTR_MOD_OP;
v_EVENT_ID APP_TO_OID_EVENT_MASTER.EVENT_ID%TYPE;
v_EVENT_TYPE APP_TO_OID_EVENT_MASTER.EVENT_TYPE%TYPE;
v_EVENT_SRC APP_TO_OID_EVENT_MASTER.EVENT_SRC%TYPE;
v_OBJECT_NAME APP_TO_OID_EVENT_MASTER.OBJECT_NAME%TYPE;
v_OBJECT_TYPE APP_TO_OID_EVENT_MASTER.OBJECT_NAME%TYPE;
v_OBJECT_GUID APP_TO_OID_EVENT_MASTER.OBJECT_TYPE%TYPE;
v_OBJECT_DN APP_TO_OID_EVENT_MASTER.OBJECT_DN%TYPE;
v_PROFILE_ID APP_TO_OID_EVENT_MASTER.PROFILE_ID%TYPE;
v_DESCRIPTION APP_TO_OID_EVENT_MASTER.DESCRIPTION%TYPE;
v_ATTR_NAME APP_TO_OID_EVENT_DETAILS.ATTR_NAME%TYPE;
v_ATTR_TYPE APP_TO_OID_EVENT_DETAILS.ATTR_TYPE%TYPE;
v_ATTR_VALUE APP_TO_OID_EVENT_DETAILS.ATTR_VALUE%TYPE;
v_ATTR_MOD_OP APP_TO_OID_EVENT_DETAILS.ATTR_MOD_OP%TYPE;
curAttrName APP_TO_OID_EVENT_DETAILS.ATTR_NAME%TYPE := '%APPNAME%';
curAttrType APP_TO_OID_EVENT_DETAILS.ATTR_TYPE%TYPE := 0 ;
curAttrModOp APP_TO_OID_EVENT_DETAILS.ATTR_MOD_OP%TYPE := 0;
attr_idx NUMBER := 1;
attr_value_idx NUMBER := 1;
saveStartID NUMBER ;
startEventID NUMBER;
v_ATTR_BVALUE VARCHAR2(2048);
BEGIN
LogTrace('New Call to GetApp Events....');
attr_list := LDAP_ATTR_LIST_V3 ();
attr_value_list := LDAP_ATTR_VALUE_LIST_V3 ();
event_list := LDAP_EVENT_LIST_V3();
SELECT NVL(LAST_EVENT_PROCESSED_BY_OID,0)
INTO LastEventProcByOID
FROM OID_PROV_PARAMS_STATUS ;
saveStartID := LastEventProcByOID;
SELECT NVL(EVENT_BATCH_SIZE,1)
INTO batchSize
FROM OID_PROV_PARAMS_STATUS ;
LogTrace('Getting ' || batchSize || ' Events From ID : '
|| LastEventProcByOID);
OPEN c_EVTS(LastEventProcByOID);
LogTrace('Opened c_EVTS Cursor');
evt_idx := 0;
LOOP
FETCH c_EVTS INTO v_EVENT_ID,v_EVENT_TYPE,v_EVENT_SRC,
v_OBJECT_NAME,v_OBJECT_TYPE,v_OBJECT_GUID,
v_OBJECT_DN,v_PROFILE_ID,v_DESCRIPTION;
EXIT WHEN c_EVTS%NOTFOUND;
LogTrace('Got Event ID: ' || v_EVENT_ID);
evt_idx := evt_idx + 1 ;
IF (evt_idx = 1)
THEN
startEventID := v_EVENT_ID;
END IF;
OPEN c_EVT_DTLS(v_EVENT_ID);
LogTrace('Opened c_EVT_DTLS Cursor');
curAttrName := 'FIRST_ATTR_TAG';
attr_idx := 1;
attr_value_idx := 1;
LOOP
FETCH c_EVT_DTLS INTO v_ATTR_NAME,v_ATTR_TYPE,
v_ATTR_VALUE,v_ATTR_MOD_OP ;
EXIT WHEN c_EVT_DTLS%NOTFOUND;
LogTrace('Fetched Attribute : ' || v_ATTR_NAME);
v_ATTR_BVALUE := '';
IF (v_ATTR_TYPE = PRASA_LDAP_NTFY.ATTR_TYPE_ENCRYPTED_STRING)
THEN
LogTrace('Encrypting Attribute: ' || v_ATTR_NAME);
v_ATTR_BVALUE := Encrypt(v_ATTR_VALUE);
v_ATTR_VALUE := Encrypt(v_ATTR_VALUE);
END IF;
IF (curAttrName != 'FIRST_ATTR_TAG')
THEN
-- Not the First row being fetched
IF (curAttrName != v_ATTR_NAME)
THEN
-- New Attribute Encountered. Need to store the existing values
-- for the previous attribute and start a new one.
attr_list.extend ;
attr_list(attr_idx) :=
LDAP_ATTR_V3 (curAttrName,
curAttrType,
curAttrModOp,
attr_value_list);
LogTrace('Added attr :' || curAttrName);
attr_idx := attr_idx+1;
attr_value_list.delete;
attr_value_idx := 1;
END IF;
END IF;
attr_value_list.extend ;
attr_value_list(attr_value_idx) :=
LDAP_ATTR_VALUE_V3 (v_ATTR_VALUE,
NULL,
LENGTH(v_ATTR_VALUE));
attr_value_idx := attr_value_idx+1;
LogTrace('Added Value to attr :' || v_ATTR_NAME);
curAttrName := v_ATTR_NAME;
curAttrType := v_ATTR_TYPE;
curAttrModOp := v_ATTR_MOD_OP;
LogTrace('curAttrName:' || v_ATTR_NAME);
END LOOP;
CLOSE c_EVT_DTLS;
LogTrace('Fetched All Attributes for Event : ' || v_EVENT_ID);
attr_list.extend ;
attr_list(attr_idx) :=
LDAP_ATTR_V3 (curAttrName,
curAttrType,
curAttrModOp,
attr_value_list);
LogTrace('Added Last attr :' || curAttrName);
attr_idx := attr_idx+1; -- Not Required
attr_value_list.delete;
attr_value_idx := 1; -- Not Required
event_list.extend;
event_list(evt_idx) := LDAP_EVENT_V3( v_EVENT_TYPE,
v_EVENT_ID ,
v_EVENT_SRC,
v_EVENT_SRC,
NULL,
v_OBJECT_NAME,
v_OBJECT_TYPE,
v_OBJECT_GUID,
v_OBJECT_DN,
v_PROFILE_ID,
attr_list);
LogTrace('Constructed Event ' || evt_idx);
attr_list.delete;
IF (evt_idx = batchSize)
THEN
LogTrace('Accumulated ' || evt_idx || ' Events.Exiting.');
LastEventSentByAPP := v_EVENT_ID;
EXIT;
END IF;
END LOOP;
LogTrace('Out Of Outer Loop ' );
CLOSE c_EVTS;
IF (evt_idx = 0)
THEN
RETURN EVENT_NOT_FOUND;
ELSE
RETURN EVENT_FOUND;
END IF;
-- EXCEPTION
-- WHEN OTHERS THEN
-- startEventID := saveStartID ;
-- LogTrace(' Error code : ' || TO_CHAR(SQLCODE));
-- LogTrace(' Error Message : ' || SQLERRM);
-- LogTrace(' Exception encountered .. exiting');
END ;
-- ==========================================================================
PROCEDURE PutAppEventStatus (event_status_list IN LDAP_EVENT_STATUS_LIST_V3)
IS
idx NUMBER := 1;
BEGIN
IF (event_status_list IS NOT NULL)
THEN
FOR idx in event_status_list.first .. event_status_list.last LOOP
INSERT INTO APP_TO_OID_EVENT_STATUS VALUES (
event_status_list(idx).event_id,
event_status_list(idx).event_status,
event_status_list(idx).event_status_msg,
event_status_list(idx).orclguid);
UPDATE OID_PROV_PARAMS_STATUS SET LAST_EVENT_PROCESSED_BY_OID =
TO_NUMBER(event_status_list(idx).event_id);
END LOOP;
LogTrace('Setting Event Success for :' ||
event_status_list(idx).event_id);
END IF;
END;
-- ==========================================================================
PROCEDURE PutOIDEvents ( event_list IN LDAP_EVENT_LIST_V3,
event_status_list OUT LDAP_EVENT_STATUS_LIST_V3)
IS
i NUMBER;
j NUMBER;
idx NUMBER := 1;
evt_cnt_idx NUMBER := 1 ;
evt_sts_idx NUMBER := 1;
event_status LDAP_EVENT_STATUS_V3;
temp1 NUMBER := 1;
temp2 NUMBER := 1;
prasa_uid varchar2(40) := NULL;
prasa_gid varchar2(40) := NULL;
prasa_groupname integer;
BEGIN
IF (event_list IS NOT NULL)
THEN
IF (event_list.count 0)
THEN
event_status_list := LDAP_EVENT_STATUS_LIST_V3();
FOR idx in event_list.first .. event_list.last LOOP
IF ((event_list(idx).attr_list IS NULL) OR (event_list(idx).attr_list.count = 0))
THEN
INSERT INTO OID_TO_APP_RECEIVED_EVENTS(EVENT_TYPE,
EVENT_ID,
EVENT_SRC,
EVENT_TIME,
OBJECT_NAME,
OBJECT_TYPE,
OBJECT_GUID,
OBJECT_DN,
PROFILE_ID)
VALUES (event_list(idx).event_type,
event_list(idx).event_id,
event_list(idx).event_src,
event_list(idx).event_time,
event_list(idx).object_name,
event_list(idx).object_type,
event_list(idx).object_guid,
event_list(idx).object_dn,
event_list(idx).profile_id);
END IF;
IF ( (event_list(idx).attr_list IS NOT NULL ) AND (event_list(idx).attr_list.count 0 ) )
THEN
FOR i in event_list(idx).attr_list.first .. event_list(idx).attr_list.last
LOOP
IF ( (event_list(idx).attr_list(i).attr_values IS NOT NULL ) AND (event_list(idx).attr_list(i).attr_values.count 0 )
)
THEN
-- prasa_sn varchar2(30);
-- prasa_cn varchar2(30);
-- prasa_uid varchar2(4000) := '';
FOR j in event_list(idx).attr_list(i).attr_values.first .. event_list(idx).attr_list(i).attr_values.last
LOOP
/* if (event_list(idx).attr_list(i).attr_name = 'cn')
then
prasa_cn := event_list(idx).attr_list(i).attr_values(j).attr_value;
elseif (event_list(idx).attr_list(i).attr_name = 'sn')
then
prasa_sn := event_list(idx).attr_list(i).attr_values(j).attr_value;
elseif (event_list(idx).attr_list(i).attr_name = 'uid')
then
prasa_uid := event_list(idx).attr_list(i).attr_values(j).attr_value;
end if; */
if (event_list(idx).attr_list(i).attr_name = 'uid')
then
prasa_uid := event_list(idx).attr_list(i).attr_values(j).attr_value;
insert into sa_user(logon) values (event_list(idx).attr_list(i).attr_values(j).attr_value);
elsif (event_list(idx).attr_list(i).attr_name = 'employeetype')
then
prasa_gid := event_list(idx).attr_list(i).attr_values(j).attr_value;
select count(*) into prasa_groupname from sa_group where group_name = prasa_gid;
if (prasa_groupname = 0)
then
insert into sa_group values (event_list(idx).attr_list(i).attr_values(j).attr_value);
end if;
end if;
if (prasa_uid is not null) and (prasa_gid is not null)
then
insert into sa_user_group(logon,group_name) values (prasa_uid,prasa_gid);
prasa_uid := NULL;
prasa_gid := NULL;
end if;
INSERT INTO OID_TO_APP_RECEIVED_EVENTS(EVENT_TYPE,
EVENT_ID,
EVENT_SRC,
EVENT_TIME,
OBJECT_NAME,
OBJECT_TYPE,
OBJECT_GUID,
OBJECT_DN,
PROFILE_ID,
ATTR_NAME,
ATTR_VALUE,
ATTR_VALUE_LEN,
ATTR_MOD_OP)
VALUES (event_list(idx).event_type,
event_list(idx).event_id,
event_list(idx).event_src,
event_list(idx).event_time,
event_list(idx).object_name,
event_list(idx).object_type,
event_list(idx).object_guid,
event_list(idx).object_dn,
event_list(idx).profile_id,
event_list(idx).attr_list(i).attr_name,
event_list(idx).attr_list(i).attr_values(j).attr_value,
event_list(idx).attr_list(i).attr_values(j).attr_value_len,
event_list(idx).attr_list(i).attr_mod_op);
END LOOP;
END IF ;
END LOOP;
END IF ;
event_status_list.extend;
event_status_list(evt_sts_idx) :=
LDAP_EVENT_STATUS_V3( event_list(idx).event_id,
'EVENT_SUCCESS',
'Successfully Processed..',
NULL);
evt_sts_idx := evt_sts_idx+1;
UPDATE OID_PROV_PARAMS_STATUS
SET LAST_EVENT_PROCESSED_BY_APP =
TO_NUMBER(event_list(idx).event_id);
END LOOP;
temp1 := event_list.first;
temp2 := event_list.count;
INSERT INTO OID_TO_APP_EVENTS_COUNT(ID,EVENT_COUNT)
VALUES (temp1,temp2);
END IF;
END IF;
END ;
-- ==========================================================================
END PRASA_LDAP_NTFY;
|