Extracting PSRM BO extension data using Materialized Views
By Rick Finley-Oracle on Apr 21, 2014
Thank you to Chocks Subramanian for putting this together!
Business Objects defined with XML schemas in PSRM can be extended to include custom data elements. These custom data extension elements are in XML format in a Business Object extension column, which is a CLOB data type in the database. When accessing this information through the PSRM application, always use the Business Object, which is easy to interact which. However, if you need to interact directly with the database (rather than the application), there are a number of ways you can access Business Object extension data stored in XML format in the BO data extension column (CLOB datatype). Of the many methods, this job aide is to provide a recommendation to use materialized view to flatten the xml element into relational column. The materialized view will be created with all the elements from the CLOB data that are required for reporting and the query predicates. To improve the performance further, indexes can be created on the columns of materialized view.
This job aid has addition information on the recommended approach: