AnsweredAssumed Answered

When adding more than 70 Entitlements to a new Role, the Request failes with ORA-01460: unimplemented or unreasonable conversion requested

Question asked by Chris Pope on Oct 31, 2019
Latest reply on Mar 5, 2020 by Ian Staines

Like • Show 1 Like1
Comment • 8

We have a new Business Role with 226 Entitlements. When applying the changes, the request that is generated has all 226 Change Items even though we have the Admin > System > Max items per change request set to 10.

The custom Approval workflow is where the error occurs, it is the same Approval Workflow that has been functioning flawlessly for two years for all our other requests. The error is:

Exception caught during script evaluation. Script 'SQL Query', ScriptID=17:WPDS, JobID=343624:WPDS, JobRef='62275', ProcessRef='WF_AP_MISO_ITBG', JobRowVersion=4, NodeName='Inspect Activities in CR', ProcessNodeUUID=6693562d-4a95-4d53-98e9-f4ba4709eae5, JobNodeID=3827829:WPDS, WorkItem=2916970:WPDS:1, FIFO=12050990, Sort Order=1. Exception was: com.workpoint.common.exception.ScriptEngineException: java.sql.SQLException: ORA-01460: unimplemented or unreasonable conversion requested
Error occurred in Statement #1 in Script ID 17:WPDS, Script Name = 'SQL Query', Script type = 'Action'. Job ID = 343624:WPDS, Process Ref = 'WF_AP_MISO_ITBG', Node Name = 'Inspect Activities in CR', WorkItem = 2916970:WPDS:1

I also tested another Role which has the same issue but with 89 Entitlements. I added 70 Entitlements and applied the changes and the request didn't fail. Then I added the remaining 19 and those also worked with no problem. So it seems 70 is the magic number.

It appears that for some reason the Max items per change request is not being enforced for the Role changes.

Anyone else experiencing this?

1 person has this question

Outcomes

Ian Staines

Oct 31, 2019 11:05 AM

You have encountered a practical limit to the complexity of the SQL in the SQL node. The limitation is not a specific number, but the aggregate size in bytes of an intermediate object that is used internally to hold the data for the SQL statement.

Is this a custom SQL node?

You may have to reconsider the design of the SQL in this node to avoid this limitation. For example you may need to use a store procedure, or split the logic across multiple nodes in some way.

This really is a workflow design issue and I cannot really advise you without knowing the actual workflow.

Actions

Chris Pope @ Ian Staines on Oct 31, 2019 2:46 PM

I agree that this is a limitation of the SQL but what I don't understand is why this was not split into multiple requests due to the Max items per change request set to 10. I expect that the 227 items would have been split into 27-28 requests.

Why are these changes to the Role not handled the same as user access requests?

Actions

Ian Staines

@ Chris Pope on Oct 31, 2019 3:20 PM

That is a separate question. I don't have an explanation for why your Max items per change request setting is not being applied to this request.

I do not have much experience with this feature as it is a global setting and most customers cannot really leverage it effectively due to this. I did search and I found no one else reporting this issue.

I would open a support case and submit this as a defect.

Actions

Chris Pope @ Ian Staines on Nov 1, 2019 12:41 PM

Yes, I've already submitted a ticket and after more than 24 hours I didn't get and acknowledgement that anyone was assigned to it. I had to email another tech to get someone to contact me about the case. I figured I might have more luck asking here.

And I did state in my original post that the issue involved the Max items per change request is not being enforced for the Role changes. Perhaps someone else has input.

Actions

Ian Staines

@ Chris Pope on Nov 1, 2019 2:07 PM

It is my assessment that the issue with Max items per change request is a defect. I will watch to ensure the case gets moved to engineering for resolution.

Actions

Padmalochan Mohanty @ Ian Staines on Mar 5, 2020 9:24 AM

We have encountered a similar issue. It is an access request from form.

Currenlty in 7.1.0 P5.

RSA Identity Governance & Lifecycle Knowledge Base, Is the fix provided in any specific version ?

Actions

Chris Pope @ Padmalochan Mohanty on Mar 5, 2020 10:33 AM

As usual, RSA has said this is "expected behavior" and isn't going to do anything about it. Which is of course not helpful in the slightest. Here are the comments they entered when they closed my case:

As per the confirmation from engineering, this is expected behavior.
Unlike other change request types, Role changes by definition contain multiple parts, the Role change itself as well as related entitlements and/or member changes (User Changes/Account changes). In a change request, all these change items must be processed together in order to maintain Role integrity. So the role management including the approvals, fulfillment, even cancellations, all must be done together for Role based provisioning.
Due to such dependencies, the setting "Max items per change request" was not implemented and not meant for Roles.
If you would like to submit an enhancement/feature request, I suggest submitting an idea by logging into RSA Link.
To submit an enhance request, we have implemented the new way to submit enhancement; it is called 'Submit An Idea'.
To submit an idea, you can log-in to link --> https://community.rsa.com/community/products/governance-and-lifecycle/ideas and click on 'Submit an Idea'.

You can create and submit new ideas and vote for those or existing ideas that other people have submitted and higher the vote, higher the chances that enhancement will be considered by Product Management Team.

So we had to update our custom workflows to minimize the size of what we are returning from our SQL queries so as to not encounter the Oracle error. In our case, we had a custom SQL node that was parsing the Access Request Details variable which was a huge block of text that exceeded the Oracle limit with larger requests so the logic was changed to query they individual items in the Change Request Details table.

I hope this is more help than what RSA provided.

Actions

Ian Staines

@ Padmalochan Mohanty on Mar 5, 2020 11:24 AM

Just to clarify, Chris reported a Java exception ORA-01460. When you say your issue is similar, is it exactly the same or just the same exception. Chris's response is specifically for the use case where a large Role change results in change request size that exceeds the internal Oracle variable size.

The ORA-01460 exception may occur for other reasons as well.

Actions

8 Replies

Ian Staines Oct 31, 2019 11:05 AM
Mark Correct
Correct Answer
You have encountered a practical limit to the complexity of the SQL in the SQL node. The limitation is not a specific number, but the aggregate size in bytes of an intermediate object that is used internally to hold the data for the SQL statement.

Is this a custom SQL node?

You may have to reconsider the design of the SQL in this node to avoid this limitation. For example you may need to use a store procedure, or split the logic across multiple nodes in some way.

This really is a workflow design issue and I cannot really advise you without knowing the actual workflow.
Like • Show 2 Likes2
Actions
- Chris Pope @ Ian Staines on Oct 31, 2019 2:46 PM
  Mark Correct
  Correct Answer
  I agree that this is a limitation of the SQL but what I don't understand is why this was not split into multiple requests due to the Max items per change request set to 10. I expect that the 227 items would have been split into 27-28 requests.
  
  Why are these changes to the Role not handled the same as user access requests?
  Like • Show 0 Likes0
  Actions
  - Ian Staines @ Chris Pope on Oct 31, 2019 3:20 PM
    Mark Correct
    Correct Answer
    That is a separate question. I don't have an explanation for why your Max items per change request setting is not being applied to this request.
    
    I do not have much experience with this feature as it is a global setting and most customers cannot really leverage it effectively due to this. I did search and I found no one else reporting this issue.
    
    I would open a support case and submit this as a defect.
    Like • Show 0 Likes0
    Actions
    - Chris Pope @ Ian Staines on Nov 1, 2019 12:41 PM
      Mark Correct
      Correct Answer
      Yes, I've already submitted a ticket and after more than 24 hours I didn't get and acknowledgement that anyone was assigned to it. I had to email another tech to get someone to contact me about the case. I figured I might have more luck asking here.
      
      And I did state in my original post that the issue involved the Max items per change request is not being enforced for the Role changes. Perhaps someone else has input.
      Like • Show 0 Likes0
      Actions
      - Ian Staines @ Chris Pope on Nov 1, 2019 2:07 PM
        Mark Correct
        Correct Answer
        It is my assessment that the issue with Max items per change request is a defect. I will watch to ensure the case gets moved to engineering for resolution.
        Like • Show 0 Likes0
        Actions
        Padmalochan Mohanty @ Ian Staines on Mar 5, 2020 9:24 AM
        Mark Correct
        Correct Answer
        We have encountered a similar issue. It is an access request from form.
        Currenlty in 7.1.0 P5.
        
        RSA Identity Governance & Lifecycle Knowledge Base, Is the fix provided in any specific version ?
        Like • Show 0 Likes0
        Actions
        Chris Pope @ Padmalochan Mohanty on Mar 5, 2020 10:33 AM
        Mark Correct
        Correct Answer
        As usual, RSA has said this is "expected behavior" and isn't going to do anything about it. Which is of course not helpful in the slightest. Here are the comments they entered when they closed my case:
        As per the confirmation from engineering, this is expected behavior.
        Unlike other change request types, Role changes by definition contain multiple parts, the Role change itself as well as related entitlements and/or member changes (User Changes/Account changes). In a change request, all these change items must be processed together in order to maintain Role integrity. So the role management including the approvals, fulfillment, even cancellations, all must be done together for Role based provisioning.
        Due to such dependencies, the setting "Max items per change request" was not implemented and not meant for Roles.
        If you would like to submit an enhancement/feature request, I suggest submitting an idea by logging into RSA Link.
        To submit an enhance request, we have implemented the new way to submit enhancement; it is called 'Submit An Idea'.
        To submit an idea, you can log-in to link --> https://community.rsa.com/community/products/governance-and-lifecycle/ideas and click on 'Submit an Idea'.
        
        You can create and submit new ideas and vote for those or existing ideas that other people have submitted and higher the vote, higher the chances that enhancement will be considered by Product Management Team.
        
        So we had to update our custom workflows to minimize the size of what we are returning from our SQL queries so as to not encounter the Oracle error. In our case, we had a custom SQL node that was parsing the Access Request Details variable which was a huge block of text that exceeded the Oracle limit with larger requests so the logic was changed to query they individual items in the Change Request Details table.
        
        I hope this is more help than what RSA provided.
        Like • Show 0 Likes0
        Actions
        Ian Staines @ Padmalochan Mohanty on Mar 5, 2020 11:24 AM
        Mark Correct
        Correct Answer
        Just to clarify, Chris reported a Java exception ORA-01460. When you say your issue is similar, is it exactly the same or just the same exception. Chris's response is specifically for the use case where a large Role change results in change request size that exceeds the internal Oracle variable size.
        
        The ORA-01460 exception may occur for other reasons as well.
        Like • Show 0 Likes0
        Actions

When adding more than 70 Entitlements to a new Role, the Request failes with ORA-01460: unimplemented or unreasonable conversion requested

Outcomes

Related Content

Recommended Content