E1: ENV: UBE: UBE Ends in Error OCI0000178 - Unable to execute - SELECT Statement When Trying to Access Foreign Table (Doc ID 2045540.1)

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.

Symptoms

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:

Datasource System User
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 DEFAULT JDE Enabled 365 0 0 2015/08/13
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:

 

Changes

 

Cause

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