This document is not intended to guide customers on how to write/put stored procedures in the database.
This document deals with possible locking (Deadlocks) or application failure which is caused by the implementation of stored procedures in the relational database system.
In general, it is hard to determine the root cause of the issue when additional routine is written outside JDE, so it is important to inform Oracle Support of any changes made at the database level (which may include: function, stored procedures, table triggers and view).
Symptoms appear in EnterpriseOne that return multiple errors/problems. One example of these issues are:
[RUNTIME] *ERROR* CallObject@44c1b9: COSE#1000 Request timeout: timeout after 300000ms host E1Logic:6015(1134) SocID:1139 PID:6004 BSFN:ShipConfirmEndDoc user:UserA Env:JPD900 [RUNTIME] *ERROR* CallObject@44c1b9: Server problem. The server may still be available, but because of state information, the entire unit-of-work must be resubmitted user:UserA Env:JPD900 [WARN ] UserA - [RUNTIME] BusFunctionAsynState.executeAsync(): async BSFN failed and need roll back!!
Notes:
Default timeout value for parameter enterpriseserverTimeout is 90,000 ms in the JAS.INI file
EnterpriseOne does not support stored procedures
You may be able to write functions, stored procedures, views and triggers
Stored Procedures are pre-compiled database queries that improve the security, efficiency and usability of the database client/server applications. Developers specify a stored procedure in terms of input and output variables. They then compile the code on the database platform and make it available to application developers for use in other environments, such as web applications. All of the major database platforms, including Oracle and SQL Server support stored procedures. The major benefits of this technology are the substantial performance gains from pre-compiled executions, the reduction of client/server traffic, development efficiency gains from code reuse and abstraction and the security controls inherent in granting users permissions on specific stored procedures instead of the underlying database tables.
Changes are as follows:
Created/called stored procedures to handle Sales Order Header transactions which update table F4201 (Sales Order Header) at 9:00 in the morning
Coincidentally you have set EnterpriseOne scheduled job at 9:00 to call R42950 - Sales Order Batch Price/Cost Update which updates the Price and Cost based on F4201/F4211 - Sales Order Header/Detail File
A deadlock has occurred with error "COSE#1000 Request timeout" in JAS, which is triggered by an add-on stored procedure against table F4201 (Sales Order Header File).
There are two outstanding messages; one from the database and the other from UBE logs as shown below.
I. On the database side, there are two queries that determine whether there is any blocking process/session:
sql> select a.owner, a.object_name, b.oracle_username, b.os_user_name, b.process, b.session_id from dba_objects a, v$locked_object b where a.object_id=b.object_id order by b.process;
Check session ID which blocks other processes/sessions
Check oracle_username which may appear different from system user ID to access JDE (for this example, JDEDV2)
(Optional) which determines if deadlock kill session causes blocking
Note: The above query has to be issued by your in-house DBA and JDE will not be responsible for the result returned.
II. UBE log may contain the following:
JDB_EXEF.c4159 JDB3300020 - Fetch not allowed. Prior successful Select operation required for this request. Exiting Fetch with Failure
Notes:
Error comes because a fetch is allowed only if there was a prior successful select
In this example, SELECT fails due to blocking through the stored procedure the client is executing
JDB_EXEF.c can vary between tools releases because it reflects the line number where the error message is implemented
For example JDB3300020 - Fetch not allowed. Prior Select for this request should be JDB_REQUEST_SELECT type and if there is a prior successful select, but it was a "REQUEST FOR UPDATE" then only a fetch with a "REQUEST FOR UPDATE" is allowed
Oracle doesn't support custom code in E1 database just like how other E1 custom objects are not supported. You can still go ahead and use it, but it will go out of scope for Oracle Support to troubleshoot issues related to it.
Remove the stored procedure from the Oracle Database Client.