error in clearing duplicate values of custom attribute

[Former Member]

Hello IDM experts,

IDM 7.1 SP7

DB: Oracle 3.0.04

I have one custom attribute "Z_REQUESTTYPE"  having same value assigned twice for many users (MX_PERSON) .

MSKEY	ATTRNAME	AVALUE	SEARCHVALUE
	Z_REQUESTTYPE	MASSLOAD	MASSLOAD
	Z_REQUESTTYPE	MASSLOAD	MASSLOAD

Removing this attribute (attribute is set as multi-value attribute) for the affected user using uIS_SetValue(mskey, 2 , "Z_REQUESTTYPE", "") or {R} operator using below statement does not work.

MSKEYVALUE           %USER_ID%

changetype                modify

Z_REQUESTTYPE     {R}

I think it would work using DELETE sql query but how to do it using IDM without using DELETE sql query.

Instead, I get below error on execution of above statement.

Error

putNextEntry failed storing<user> Exception from Modify operation:com.sap.idm.ic.ToPassException: ToIDStore.modEntry failed modifying entry '<user>'. IDStore returned error message: "DATABASE EXCEPTION:java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rowsORA-06512: at "MXMC_OPER.MXI_DELETEMULTI", line 265ORA-06512: at "MXMC_OPER.MX_UTILSETATTRIBUTE", line 1601ORA-06512: at line 1" when clearing attribute Z_REQUESTTYPE

However, I could delete this attribute with different values for affected users as shown below.

MSKEY	ATTRNAME	AVALUE	SEARCHVALUE
	Z_REQUESTTYPE	MASSLOAD	MASSLOAD
	Z_REQUESTTYPE	HR	HR

I tried to create same value of this attribute Z_REQUESTTYPE twice for a user by executing uIS_SetValue(mskey, 2 , "Z_REQUESTTYPE", "MASSLOAD") or {A} operator two times but it does not add same value twice to this attribute for the user.

MSKEYVALUE           %USER_ID%

changetype                modify

Z_REQUESTTYPE     {A}MASSLOAD

I wonder how I have many users with same value twice for this attribute.

If I try to create duplicate entries using insert sql query, then it is possible.

Kindly assist on how to delete both same values for this attribute for all affected users.

Your help is greatly appreciated.

Thanks & Regards,

Pradeep

[Former Member · Accepted solution]

Hi Pradeep,

  You can have this problen, if you are using two tasks for updating the values, the transaction used to write the value for your custom attribute into mxi_value table don't know that secont task is trying to update value, so both tasts write the same value into IdM. Then, when you are trying to remove the duplicated value using a task in IdM you have an error, becouse for IdM is a problem having duplicated values writen in attribute. To delete the duplicated values from table mxi_value you have to user SQL and delet them directly from the database.

Kind Regards,

Simona Lincheva

[Former Member · Accepted solution]

Thank you 🙂

I will be using Delete query to remove these entries as it can not be done via IDM.

Regards,

Pradeep

[Former Member · Accepted solution]

Hi Simona,

I executed the delete sql query as shown below

DELETE FROM mxi_values WHERE mskey = <usermskey> and attr_id = 642

642 is attribute id of the attribute .

I can see that entries are removed from mxiv_sentries & mxi_values for the user but values are still visible in the IDM UI as shown below.

Is there anything else to be done ?

Your help is greatly appreciated.

Regards,

Pradeep

[Former Member · Accepted solution]

Hi Pradeep,

  Check the UI task that displays the Request Type and see what attribute you have set there, if it is the same you are deleting. Also, be sure that after deleting the attribute you commit the deletion.

BR,

Simona

[Former Member · Accepted solution]

Hi Simon,

Sorry, I forgot to mention commit as it is oracle DB.

Now it works.

Thank you. 🙂

Regards,

Pradeep

[Former Member · Accepted solution]

Simon says no problem!