My Oracle Support Banner

Discoverer 11g Querying Against An E-Business Suite 12 Multi-Org VPD Protected View With ORG_ID Returns No Data (Doc ID 732826.1)

Last updated on OCTOBER 12, 2017

Applies to:

Oracle Discoverer - Version to [Release 10.1.2 to 11g]
Information in this document applies to any platform.
*** Checked for relevance on 04-Dec-2014 ***


When creating a custom Discoverer workbook or using SQL*Plus (or equivalent) against a custom view or a standard pre-seeded view (e.g. - oe_orders_headers_v) that is protected with a Virtual Private Data Base (VPD) context policy based on org_id,  no data is returned.

When using SQL*Plus (or equivalent), no data is returned when run via a similar way that Discoverer runs.  For example, following: <Note 279736.1> 'How To Run SQL From An Applications Mode Discoverer Workbook In SQL*PLUS'.

exec FND_GLOBAL.APPS_INITIALIZE (user_id, resp_id, appl_id, security_group_id );

select count(*) from apps.oe_order_headers_v

However, if the VPD policy is passed with the organization ID, then data is returned.

For example:

exec FND_GLOBAL.APPS_INITIALIZE (user_id, resp_id, appl_id, security_group_id );

exec apps.mo_global.set_policy_context('S',81);

select count(*) from apps.oe_order_headers_v


To view full details, 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 a vibrant support community of peers and Oracle experts.