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).