E1: ENV: UBE: UBE Ends in Error OCI0000178 - Unable to execute - SELECT Statement When Trying to Access Foreign Table
Last updated on AUGUST 02, 2017
Applies to:JD Edwards EnterpriseOne Tools - Version 9.1 and later
Information in this document applies to any platform.
You are not able to access the foreign table defined in external database in UBE whereas it can be accessed via interactive applications.
Consider the two table:F0006 - standard Business Unit Master table and F0006Z1(F0006Z1 is a copy of F0006 table). F0006Z1 is a foreign table that resides in database other than enterprise one master database.
Below are the datasources used:
Normal Datasource - Business Data - DV
Foreign Datasource - Business Data - FRN
Below is list of system users used in this example:
|Buisiness Data - DV||JDE|
|Buisiness Data - FRN||E1TEST|
System User JDE is used to access master database whereas system user E1TEST is used to access Foreign Database.
Here are the records in the P98OWSEC application for the User : SBC
|UserID||Datasource||System User||User Status||Change Frequency||Allowed attempts||Rerty Count||Security Changed|
|SBC||Business Data - FRN||E1TEST||Enabled||365||0||0||2015/08/13|
OCM Mappings for F0006Z1 added under system as well as server map data sources.
When a user logged in and tries to access F0006Z1 via UTB or Databrowser, the system user E1TEST is used and able to open the table F00006Z1 successfully.It also works when the table is accessed via interactive applications.
However when UBE tries to access table-F0006Z1 , it always uses JDE system user to connect to foreign database (Table - F0006Z1). UBE ends in error with error status "E" in WSJ (Work with Submitted Job). The record for system user E1TEST in P98OWSEC is not considered.
Error message in UBE debug Log:
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms