Question

BPA Workflow error: Cannot commit in Current PL/SQL Session ORA-06512

  • 11 December 2023
  • 13 replies
  • 339 views
B
Rank
Userlevel 5
Badge +15

Hello,

 

I am currently facing an issue when automatically trying to unblock a customer order via a workflow.

I am doing the following call:

which has worked in the past to unblock a blocked customer order, but results in the following error:

ORA-00034: cannot COMMIT in current PL/SQL session ORA-06512: at "IFSAPP.ORDER_COORDINATOR_GROUP_API", line 1762 ORA-06512: at "IFSAPP.CUSTOMER_ORDER_API", line 12697 ORA-00034: cannot ROLLBACK in current PL/SQL session ORA-06512: at "IFSAPP.CONNECT_CUSTOMER_ORDER_API", line 2381 ORA-00034: cannot SET SAVEPOINT in current PL/SQL session ORA-06512: at "IFSAPP.CONNECT_CUSTOMER_ORDER_API", line 2368 ORA-06512: at "IFSAPP.CONNECT_CUSTOMER_ORDER_API", line 2368 ORA-06512: at "IFSAPP.CONNECT_CUSTOMER_ORDER_API", line 272 ORA-06512: at "IFSAPP.CUSTOMER_ORDER_FLOW_API", line 266 ORA-06512: at "IFSAPP.CUSTOMER_ORDER_API", line 16585 ORA-06512: at "IFSAPP.CUSTOMER_ORDER_API", line 13182 ORA-06512: at "IFSAPP.CUSTOMER_ORDER_API", line 6152 ORA-06512: at "IFSAPP.CUSTOMER_ORDER_API", line 6169 ORA-06512: at "IFSAPP.CUSTOMER_ORDER_API", line 6191 ORA-06512: at "IFSAPP.CUSTOMER_ORDER_API", line 12650 ORA-06512: at "IFSAPP.CUSTOMER_ORDER_API", line 12715 ORA-06512: at "IFSAPP.CUSTOMER_ORDER_API", line 12721 ORA-06512: at "IFSAPP.CUSTOMER_ORDER_API", line 8487 ORA-06512: at "IFSAPP.HANDLE_BLOCKED_CUSTOMER_ORDERS_SVC", line 712 ORA-06512: at "IFSAPP.HANDLE_BLOCKED_CUSTOMER_ORDERS_SVC", line 622 ORA-06512: at line 2

 

Any and all help would be greatly appreciated.

 

Thanks,
Bryan

13 replies

B
Rank
Userlevel 3
Badge +5

It seems this pl/sql action has commits within the logic. These kind of transaction handling cannot be used with workflows. Reason is, these commits interfere with the workflows ability to roll back data in case of a workflow error. This is mentioned in tech docs(known limitations for workflows).

K
Rank
Userlevel 4
Badge +9

@Buddhi Gunasekara  any alternative to work around this? Or any other suggestions how you can solve these type of issues?

Do we need to use an alternative API? Event action?

Marcel.Ausan
Rank
Userlevel 6
Badge +15

Indeed this limitation makes Workflow almost unusable for larger automations cause at some point some of the PL/SQL procedures will have a COMMIT / SAVEPOINT / ROLLBACK.

See below the link where IFS mentions the known limitations.

https://docs.ifs.com/techdocs/23r1/040_tailoring/500_business_process_automation/200_workflow_limitations/

 

https://www.linkedin.com/in/marcelausan/
dsj
Rank
Userlevel 7
Badge +20

Hi @bdoucette 

 

Since you mentioned that it has worked in the past, the problem could be a business logic error, but you don’t see the actual error due to implicit commit.

Try calling the projection action with the same payload using Postman and see if you could get the real error.

 

Hope it helps!

Damith

DSJ - https://dsj23.me
K
Rank
Userlevel 4
Badge +9

@Marcel.Ausan  @dsj 
Any idea how to identify when an API

 will have a COMMIT / SAVEPOINT / ROLLBACK.

 

As it would be nice to just know it before we start to use an API?

Marcel.Ausan
Rank
Userlevel 6
Badge +15

@kvbe not easy to identify. The thing is that the backend logic can be quite complex for some processes. And the logic might start from one PL/SQL package and jump across different packages and maybe the commit/savepoint is only in the 3rd - 4th PL/SQL package called.

At some point I was trying to setup a Workflow and I got this error. It was a simple process so I couldn’t believe the API I was using has a commit, so I debugged the PL/SQL procedures until I got to a savepoint :( in the logic.

https://www.linkedin.com/in/marcelausan/
B
Rank
Userlevel 5
Badge +15

Hi @dsj 

I’ve attempted to call the same API call via postman but the only response I get is

 

{

    "error": {

        "code": "UNEXPECTED",

        "message": "Unexpected internal server error occurred."

    }

}

dsj
Rank
Userlevel 7
Badge +20

Hi @bdoucette 

 

Append ?odata-debug=json to your request url and see if you could get the error stack.

 

Hope it helps!

Damith

DSJ - https://dsj23.me
D
Rank
Badge

Hi, I have a same error. 

I am doing the following call:

and I get this error:

ORA-00034: cannot COMMIT in current PL/SQL session ORA-06512:

I checked pl/sql and indeed one of the procedures calls COMMIT. I tried call the projection action with the same payload using Postman and it worked fine.

So if we cannot call such actions (including COMMIT), can we call it in an asynchronous way?

 

Thanks,

Daniel

B
Rank
Userlevel 3
Badge +5

@daniel.boczar 

Above limitation applies to asynchronous workflows as well. So you can’t use workflow to call the action/function you are calling.

B
Rank
Userlevel 5
Badge +15

@Buddhi Gunasekara 

What are the alternatives that a customer can use? IFS has refused to respond to any of the other community posts/comments about this issue. It seems that IFS doesn’t care about the end user or what issues their limited system causes as long as people keep using their system/paying them. This is clear as IFS R&D has refused to address this issue “short and medium term” as well as this not being on their roadmap even though they know it causes major automation issues for new customers and especially customers coming from Apps that had automation previously in place.

B
Rank
Userlevel 3
Badge +5

@bdoucette 

I'm sorry if we made you feel that way. We understand your situation.
Whatever actions done in a workflow should be atomic. With commits in 
the workflow process, if a workflow task fails, workflow might revert some changes,
and some changes will persist(changes which are explicitly committed). This data inconsistency will affect businesses and that is why workflow doesn’t allow commits in the process.
I wish we had  a better solution, but only alternative at the moment is to find another API that has the same functionality without commits.

B
Rank
Userlevel 5
Badge +15

Hi Buddhi,

 

I understand why the error is occurring, but this is more of an issue with the BPA Camunda system interacting with IFS’s projection calls rather than a proper limitation. Surely there could be some alternative to fix this, but the issue is that IFS shuts down customers, doesnt reply to them, and just says “deal with it, we won’t work on this inherently limiting issue that we know people are struggling with” It’s a blatant disregard for the care of their customers.

 

-Bryan

Reply


Terms & ConditionsCookie settings