How to solve Nonexistent table issue while extracting BICC VO Query in Oracle Fusion Cloud

In my previous post, I explained how to retrieve database query for BICC VO in Oracle Fusion Cloud

Sometimes this method won't work as not all VOs are designed the same way.

There's a chance that you'll get below error:

ERROR
-----------------------
. [nQSError: 27047] Nonexistent table

This is expected behavior as the old VO's are available in the RPD repository (metadata of the BI Server) and hence SELECT_PHYSICAL works fine.

The VO which used above is trying to access in this case is a BICC VO and these BICC PVO names end with the string (.ExtractPVO). These BICC VO's are not part of RPD and directly based on ADF. So SELECT_PHYSICAL doesn't work.

So to overcome this error, you need to use the syntax as below and you should get the results without any error:

select * from
EXTERNAL('ADF','"oracle.apps.fscm.model.analytics.applicationModule.FscmTopModelAM_FscmTopModelAMLocal"."Connection Pool"').'<VO Name>' as t
FETCH FIRST 100 ROWS ONLY

e.g.

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