In 11i we had views on these tables. It requires setting context in order to fetch data from these views. This is for security concerns as these objects holds transaction details.
The SQL command to set the ORG_ID prior to running a script is:
SQL> EXECUTE DBMS_APPLICATION_INFO.SET_CLIENT_INFO(&ORG_ID);
Enter the org_id when prompted.
If using Toad:
In R12 oracle uses VPD (Virtual Private Database) to secure these transactional data .This is one of major difference in application architecture between 11i and R12.
In order to retrieve data from transactional objects, set policy context first ( as below ) –
MO_GLOBAL.SET_POLICY_CONTEXT(‘MODE’ CHAR(1),ORG_ID NUMBER);
MODE – This is either “S” – For Single Operating Unit OR “M” – For Multiple Operating Unit
Org_ID – Operating unit (Value from column ORG_ID in all transactional objects)
This is mandatory for “S” mode.