Are using an output parameter declared as nvarchar(max) in the stored procedure? I need to try the same thing here to understand why.

Using threads is never recommended in OD. It would not automatically solve any problem, and it is not a trivial task for a lot of developers. In most of the situations, it is not necessary. In your case, you would hope to move on to the next node while a stored procedure is called asynchronously. However, if the stored procedure call is taking a long time and keeping the thread waiting, you have no way to stop the thread. In heavy load, you would have all these threads hung. I would say you need to go back to the basic. You would need to find out why the stored procedure call would stall. If it happens a lot, you need to fine tune it. Or maybe it's because the network is not stable. You may need to look at that problem as well. If it's just a situation in heavy load, ask if your load test is reasonable given the resources you have or you may need to scale it.

Are you saying that you test executing the same stored procedure in the SQL Query tab of the DBOP editor and you get 9000+ characters? That would be strange because the runtime is not doing anything different except it is executing through Tomcat connection pool. I don't think there is any limitation there.

You need to make sure the thread you create can always exit otherwise you would have hung thread, in which case in heavy load, you lead resource and cause the app server to crash. OD has no management concerning the these threads you create. It's all at your own discretion.

You may create a thread to call your own code or the dbop class but not the data node, which is a servlet. It's doable.

Check on the sql server end to see if the request reaches the server.

It looks like a network issue to me. Does the query take a long time? Maybe try to look on the sql server end to see the response time from the server.

You would need to have all the data sources pre-defined. Then in the constructor of the dbop java class, it's the matter of changing the data source name in the setDataSourceName method. You need to customize the code right after the "//}}END:CLASS:CONSTRUCTOR" marker to avoid the code getting overwritten by code generation.

It could be a temporary connection issue with EPMS. There is a file named vpapplogclient.log under the Tomcat directory. It could be hidden in a subdirectory; you may need to search for it. You should be able see some error log there. Please post the logs you see in the file.

I can tell you this is not in OD's control. Our code is simply executing the statement and wait for it's return all in one thread. If JDBC returns with an exception, it would be thrown right away in OD, and you see the exception logged right away. It's JDBC that has to wait till the stored procedure to finish. You may want to try tweak the way how you simulate the delay to see if it makes a difference. If you have Oracle support, you should post a question there.

I am kind of confused by what you saying. If you set the time out to 5 seconds, and the procedure is take 30 seconds to finish, would you can the cancel exception before the 30 seconds?

Here is a thread I found that is related

https://community.oracle.com/thread/552257

It's a bit old but I think the behavior or mechanism used has changed

I see...it's the 5 second delay that you are seeing. But OD has nothing to do with the 5 seconds. It's not doing anything but waiting for the execution to return. I am baffled by this too. I am pretty sure it's inside the jdbc driver. I will do some research and see what I can find

So you've gotten an exception due to setting the timeout. That's where the operation ends. I am not sure what you mean by connection remains. You would need to handle the exception by using try/catch surrounding the dbop call in the data node to move forward to whatever next in the flow.

Oh, I see, this is what we expect you to do. You confused me earlier by say you try to run the select statement with the function - "Select loan.checkid(1234567890) from dual".

I think there maybe a bug in display the return value in a function in the UI. I can assure you that if runtime works, it's fine because the UI and runtime are using different code. UI is more for validating the DBOP at design time.