Query Builder reverts to Original Schema in EBS R12.2 Application
Posted: 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 ...
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 ...