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 Nov 1, 2019 by Ian Staines

Like • Show 0 Likes0
Comment • 5

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?

No one else 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

5 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 1 Like1
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

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