OWB - Public Views (metadata)

About

The Warehouse Builder provides a set of pre-built views for both the design and runtime environments. These views are called the Warehouse Builder Public Views and are the API alternative to using the Repository Browser described in the Oracle Warehouse Builder User's Guide. Use these views to access to metadata and data stored in Warehouse Builder repositories.

They are stored in the Owb repository user.

Articles Related

Prerequistes

Since 11.2, if you want to connect to an other workspace than your default one, you have to call this procedure:

wb_workspace_management.set_workspace(<wksp_name>, <wksp_owner>)

-- wb_initialize_workspace is also an internal function that can do it for you
wb_initialize_workspace;

With OWBSYS, you will get default no result.
You must have the ACCESS_PUBLICVIEW_BROWSER system privilege to retrieve useful information from the pubic views. Otherwise, you will get “0 rows returned.”

Design Views

Mapping View

ALL_IV_XFORM_MAPS	The mapping
ALL_IV_XFORM_MAP_COMPONENTS	The operators
ALL_IV_XFORM_MAP_PARAMETERS	The columns of the operators

Run-time Views

Support

ORA-22835: BUFFER TOO SMALL ON ALL_IV_XFORM_MAP_PROPERTIES

When you query the view public view ALL_IV_XFORM_MAP_PROPERTIES, you may fired this error (version 10.2.0.3.33).

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 7675, maximum: 4000)
22835. 00000 -  "Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: %s, maximum: %s)"
*Cause:    An attempt was made to convert CLOB to CHAR or BLOB to RAW, where
           the LOB size was bigger than the buffer limit for CHAR and RAW
           types.
           Note that widths are reported in characters if character length
           semantics are in effect for the column, otherwise widths are
           reported in bytes.
*Action:   Do one of the following
           1. Make the LOB smaller before performing the conversion,
           for example, by using SUBSTR on CLOB
           2. Use DBMS_LOB.SUBSTR to convert CLOB to CHAR or BLOB to RAW.

Searching in My Oracle Support, I found the bug 9909971 - ORA:22835 WHILE TRYING TO SELECT FROM THE VIEW ALL_IV_XFORM_MAP_PROPERTIES but unfortunately no patch.

This error find its source in the view SUB_ALL_PERSISTED_PROPS. It's an UNION ALL of a lot of sub-query and particularly this one:

SELECT 
   propertyOwner,
   logicalName,
   position,
   name,
   elementId,
   classname                      AS objType,
   NVL(strongTypeName, classname) AS objClass,
   creationTimeStamp              AS created,
   createdBy                      AS createdBy,
   updatedBy                      AS updatedBy,
   updateTimeStamp                AS updated,
   TO_CHAR(value)                 AS value
FROM 
   CMPCLOBPropertyValue_v

To fix this issue, replace this TO_CHAR(value) by DBMS_LOB.SUBSTR(value,3990,1).