Solving "[nQSError: 27047] Nonexistent table" When Querying a PVO
When querying Public View Objects (PVO), developers can encounter the dreaded error:
[nQSError: 27047] Nonexistent table: "TABLE_NAME"Why Does This Happen When Querying a PVO?
In Fusion Cloud, many data objects—especially Public View Objects (PVOs) exposed by Oracle Application Development Framework (ADF)—are not part of the RPD. For these, the typical SELECT_PHYSICAL or logical SQL syntax fails because the BI Server cannot resolve the table name in its metadata, resulting in the “Nonexistent table” error.
Solution: Use EXTERNAL ADF Syntax
To successfully extract data from a PVO, you need to use the EXTERNAL('ADF', ...) function in your SQL data set. This connects directly to the ADF layer and retrieves data from the specified view object.
Example Syntax
SELECT * FROM
EXTERNAL('ADF',
'\"oracle.apps.fscm.model.analytics.applicationModule.FscmTopModelAM_FscmTopModelAMLocal\".\"Connection Pool\"'
).'FscmTopModelAM.FinExtractAM.ArBiccExtractAM.ReceivableActivityExtractPVO' AS t
FETCH FIRST 100 ROWS ONLY
Replace the model and PVO name with the appropriate values for your requirement. You may need to grant access to the correct connection pool and ensure your BI Publisher data source is set to “Oracle BI EE”.
Key Points
Use the full application module and view object name (e.g.,
FscmTopModelAM.FinExtractAM.ArBiccExtractAM.ReceivableActivityExtractPVO).Use
EXTERNAL('ADF', '<Connection Pool Path>')—do not useSELECT_PHYSICALfor PVOs, as this only works for objects in the RPD.Confirm user or role permissions on the relevant connection pool in BI Publisher or OTBI Administration.
When Should You Use This Approach?
For custom extracts involving BICC PVOs or other Fusion public view objects not mapped in the RPD.
When standard SQL fails with nonexistent table errors but you know the object exists in the ADF layer.
For direct querying of Oracle Fusion Cloud transactional models where the PVO is only accessible via ADF.
Understanding the difference between RPD-exposed tables and ADF-based PVOs is essential for advanced Oracle Fusion Cloud reporting. By using the EXTERNAL ADF syntax, report developers can bypass nQSError: 27047 and unlock direct access to valuable data for BI Publisher, OTBI, and integration use cases.
Need some assistance?
Ready to bring clarity to customer, supplier and employee communications? With BI Publisher, we specialise in customising customer, supplier, and employee-facing documents such as invoices, purchase orders, payslips, and statements to meet your exact branding, compliance, and operational needs. From layout design to data logic enhancements, we help you deliver clear, consistent, and impactful outputs across your organization. Contact us today to discover how our expertise in BI Publisher can streamline your document processes and elevate your business communications.
