Modeling Binary Large Object (BLOB) Data and Character Large Object (CLOB) Data

Learn how to model binary large object (BLOB) data and character large object (CLOB) data in the Oracle BI repository.

CLOB data is a large plain text document in any character set. The supported BLOB image types are: GIF, PNG, TIFF, JPEG, and BMP. BLOB formats not supported are: PDF, audio, or video.

  1. Import the physical table containing the BLOB or CLOB data from the data source using the Import Metadata Wizard. The default data type for BLOB columns after the import is LongVarBinary, while for CLOB columns it is LongVarChar. After import, open the Physical Column dialog for the BLOB or CLOB column and change the Length field to be the desired length, but ensure that it does not exceed the current Oracle BI Server MaxFieldSize limit of 32 KB. Note that this 32 KB limit is also a limitation of the Microsoft Internet Explorer browser.
  2. Configure physical joins as necessary. For example, assume there is one table called employees, and another table called Employee_Images that has two columns, employeeid and employee_image (the BLOB column), where employeeid is the primary key. In this situation, you would create a physical join between the employees and Employee_Images tables.
  3. Drag the BLOB or CLOB column to the Business Model and Mapping layer. For example, in the situation described in the previous step, you would drag the employee_image physical column to the employee logical table source. Doing this generates a logical column Employee_Image.
  4. To ensure that the Oracle BI Server does not generate a group by or order by on the resulting logical column, configure a physical lookup for the logical column. For example: 
    lookup(DENSE "MYDB"."MySchema"."EMPLOYEE_IMAGES"."EMPLOYEE_IMAGE", 
"MYDB"."MySchema"."Employees"."EmployeeID")
  5. In the General tab of the Logical Column dialog for the logical BLOB or CLOB column, configure the Descriptor ID column as needed. For our example column Employee_Image, you would configure the descriptor ID column to use EmployeeID. Doing so ensures that Presentation Services uses EmployeeID instead of Employee_Image when generating filters.
  6. Still in the General tab of the Logical Column dialog for the logical BLOB or CLOB column, configure the Sort order column as needed. For our example column Employee_Image, configure the sort order column to use employeeID. Doing so ensures that the Oracle BI Server orders by EmployeeID when the order by clause contains Employee_Image.
  7. Save the changes.