orphan privileges removal - DB stored procedure

[Sankar_Aravind]

Hi All,

Good day.

For removing the Orphan privileges, i have tried the below steps same as in the note, but still the PRIVs are not removed from DB.

The source tab query actually given output, so seems not the issue with Source.

Please suggest if i missed some where.

Source :

select L.mcUniqueId, E.mcIDStore from IDMV_LINK_EXT L INNER JOIN IDMV_ENTRY_SIMPLE E ON L.MCTHISMSKEY = E.MCMSKEY where MCTHISMSKEYVALUE = 'XXXX' and MCORPHAN = 1

Des:

$FUNCTION.sap_core_callStoredProcedure(mxi_deleteOrphanAssignment!!'%mcUniqueId%', '%mcIDStore%', 0, 'ADDITIONAL PROCESS INFORMATION')$$

pass Successful. But it shown ToODBC

Pass: Remove orphan PRIVs

---

Parameter Value Pass type ToODBC

Thanks,

Aravind.

[Sankar_Aravind]

Additionally, in the destination tab, if i mention the any uniqueID particularly (like below), that is also working fine. I mean the orphan PRIVs are getting removed. but when i mention '%mcUniqueId%', its not picking.

$FUNCTION.sap_core_callStoredProcedure(mxi_deleteOrphanAssignment!!'12345', '%mcIDStore%', 0, 'ADDITIONAL PROCESS INFORMATION')$$

[alexanderbrietz · Accepted solution]

Hi Aravind,

I use the stored procedure within a job in a toDatabase-Pass. My source is similar to yours (no restriction on L.MCTHISMSKEYVALUE and L.MCORPHAN != 0). In my destination I use %MCUNIQUEID% without single quotes. Same applies for %MCIDSTORE%.

$FUNCTION.sap_core_callStoredProcedure(mxi_deleteOrphanAssignment!!%mcUniqueID%,%mcIDStore%,0,'remove orphaned assignments - $ddm.datetime8601')$$

This works fine for me.

HTH

Regards,

Alex

[Sankar_Aravind · Accepted solution]

Hi Alex,

Thank you for the details.

Unfortunately, i received the issue even after i copy pasted the exact entry as you mentioned.

$FUNCTION.sap_core_callStoredProcedure(mxi_deleteOrphanAssignment!!%mcUniqueID%,%mcIDStore%,0,'TEST')$$

i received the below error.

Error SQL Update failed. SQL:call mxi_deleteOrphanAssignment (,,0,'TEST')
java.lang.Throwable: ORA-00936: missing expression Error putNextEntry failed storingNo_DeltaKey

[alexanderbrietz · Accepted solution]

According to your error message the expression %mcIDStore% is an empty string. Could you try with passing the number of your ID-Store? If you use standards with ID-Store it should be 1.

Can you also check the source statement for typos within %mcIDStore% and maybe copy/paste it again?

[Sankar_Aravind · Accepted solution]

Hi Alex,

i actually checked the query in SQl and it given me the results, i even tried with if this is case sensitive any.

Source :

select L.mcUniqueId, E.mcIDStore from IDMV_LINK_EXT L INNER JOIN IDMV_ENTRY_SIMPLE E ON L.MCTHISMSKEY = E.MCMSKEY where MCTHISMSKEYVALUE = 'XXXX' and MCORPHAN = 1

Also, just to make sure the syntax correct, i have passed the parameter values like below

$FUNCTION.sap_core_callStoredProcedure(mxi_deleteOrphanAssignment!!'12345', '%mcIDStore%', 0, 'ADDITIONAL PROCESS INFORMATION')$$

and it worked.

So its seems not passing mcUniqueID from source, not sure why.

Also in my previous error message, i did not given the single quotes, that's why it did not capture either unique ID or ID store value. But with single quotes and when i passed the values directly, then its working.

[alexanderbrietz · Accepted solution]

Okay then, you have to troubleshoot the source query. Maybe just use a toASCII-Pass to write the results into a file or another toDatabase-Pass to write them into a temporary table. After that you should find something that is causing the problem.

Maybe you want to add an L. to your where statement with MCTHISMSKEYVALUE and MCORPHAN and keep in mind that this is case sensitive in Oracle DB.