I have got the stored procedure details from App team. I think they have created a array of 4 varchar like below:
CREATE OR REPLACE TYPE FASDDBA01.FIELDARRAY AS OBJECT ( field1 VARCHAR2(100), field2 VARCHAR2(100), field3 VARCHAR2(100), field4 VARCHAR2(100) );
The java code extract existing entitlement list, add the entitlement for grant access, split the entitlement based on ":" character and convert them into a array.
The java code extract existing entitlement list, remove the entitlement for revoke access, split the entitlement based on ":" character and convert them into a array.
The store procedure first delete all the entitlements, loop through the no of entries in the Array and insert to a table.
For example:
The entitlement would like below:
Energy Infra:Oil_and_Gas:O&G Digital Solutions:O&G Digital Solutions
Aviation:Aviation:Aviation:Aviation
The array would like below:
responsibilityList := FIELDARRAY('Energy Infra', 'Oil_and_Gas', 'O&G Digital Solutions', 'O&G Digital Solutions'),
('Aviation', 'Aviation', 'Aviation', 'Aviation'));
Stored procedure sample(it's not the full stored procedure):
PROCEDURE CREATE_MODIFY_USER(str_ssoId IN VARCHAR2,
str_Responsibilites in FIELDARRAY)
IS
num_PersonId NUMBER := 0;
num_Temp_Var NUMBER :=0;
SELECT PERSON_ID
INTO num_PersonId
FROM T_VERT_PERSON
WHERE SSO = str_ssoId;
FOR idx IN 1..str_Responsibilites.COUNT LOOP
--str_Responsibilites(idx)(1)
BEGIN
-- Validates the OpUnit is valid
SELECT COUNT(OP.ELEMENT_ID) INTO num_Temp_Var
FROM T_VERT_HIERARCHY OP,
T_VERT_HIERARCHY SB,
T_VERT_HIERARCHY BS,
T_VERT_HIERARCHY SG
WHERE OP.PARENT_ELEMENT_ID = SB.ELEMENT_ID
AND SB.PARENT_ELEMENT_ID = BS.ELEMENT_ID
AND BS.PARENT_ELEMENT_ID = SG.ELEMENT_ID
AND OP.ELEMENT_ID = str_Responsibilites(idx)(4);
IF (num_Temp_Var = 0) THEN
RAISE INVALID_OP_UNIT_VALUE;
END IF;
--Loads the Responsibility based on OpUnit
INSERT into T_VERT_RESPONSABILITY(ELEMENT_ID, Person_ID, Created_By, Created_On)
VALUES(str_Responsibilites(idx)(4), num_PersonId, 'IDM', sysdate);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
-- Do Nothing
num_Temp_Var := 0;
END;
num_Temp_Var := 0;
END LOOP;
END CREATE_MODIFY_USER;
Java code to split:
public List<String> splitEntitVal(String value) {
List<String> argList = new ArrayList();
StringTokenizer st = new StringTokenizer(value, ":");
while (st.hasMoreTokens()) {
argList.add(st.nextToken());
}
return argList;
}
Java code to convert to Array:
public Array getRequestedRoles(List<String> groupOrRolesList) throws SQLException {
count = groupOrRolesList.size();
String[][] respValue4 = new String[count][4];
log.debug("count" + count);
int i = 0;
for (String attrValList : groupOrRolesList) {
roleList = this.splitEntitVal(attrValList);
log.debug("roleList" + roleList.size());
log.debug("roleList Values" + roleList.toString());
respValue4[i][0] = roleList.get(0);
respValue4[i][1] = roleList.get(1);
respValue4[i][2] = roleList.get(2);
respValue4[i][3] = roleList.get(3);
log.debug("in getRequestedRoles" + respValue4);
i++;
}
responsibilityList = convertConnection().createOracleArray("FASDDBA01.FIELDARRAY", respValue4);
return responsibilityList;
}
