Query Builder reverts to Original Schema in EBS R12.2 Application

Discussion of open issues, suggestions and bugs regarding database management and development tools for Oracle
Post Reply
Quindici
Posts: 1
Joined: Mon 14 Mar 2022 16:00

Query Builder reverts to Original Schema in EBS R12.2 Application

Post by Quindici » Mon 14 Mar 2022 16:35

Hello,

We use Oracle EBS and the current version of that product REQUIRES that SQL queries are made against a Synonym that is owned by the APPS Schema for all database objects. So a Table like APPLSYS.FND_USER now has a VIEW APPLYSYS.FND_USER# and a SYNONYM in APPS like APPS.FND_USER.

All this convolution is to support EDITIONING and works perfectly well in practice.

the following simple SQL retrieves data from the Synonym owned by the APPS Schema (APPS.FND_USER)

SELECT *
FROM FND_USER

-- or --

SELECT *
FROM APPS.FND_USER

However, whenever we invoke "Query Builder" tool it changes the SQL like this. (note that it now has the OWNING SCHEMA of the ORIGINAL VIEW)

SELECT *
FROM APPLSYS.FND_USER#

Whilst this will also work perfectly well, it is NOT Oracle EBS R12.2 standards compliant to retrieve data in this way. I am forced to go back into the TEXT editor and remove all the SCHEMA Owners and "#" suffixes after every use of the Query Builder tool.

Is there some way to prevent this behaviour, which is very frustrating?

Thanks,

George ...

pestoprojector
Posts: 1
Joined: Wed 22 Jun 2022 08:13

Re: Query Builder reverts to Original Schema in EBS R12.2 Application

Post by pestoprojector » Wed 22 Jun 2022 08:18

Although this will also operate very well, retrieving data in this manner does NOT comply with the requirements that are required by Oracle EBS R12.2. After using the Query Builder tool, I am required to exit out of it and go back into the TEXT editor, where I must then delete all of the SCHEMA Owners and the "#" suffixes. play tic tac toe

Post Reply