- When trying to preview tables with CLOB/BLOB datatype in PRD, we receive an error;
- When creating a custom report there is the need to retrieve data from a column with BLOB data type;
SuccessFactors Learning Management System (LMS)
Plateau Report Designer
Reproducing the Issue
- Let’s say you want to query the table PA_USER_PREFERENCE. Your query is therefore SELECT * FROM PA_USER_PREFERENCE but you receive an error upon execution.
- Doing a SELECT * in PRD might work for some tables, but is surely not advisable. Only query for the fields that you want to e.g. SELECT USER_ID, CURRENCY_CODE FROM PA_USER_PREFERENCE
- This will not throw error so you can identify the cause of the issue is the BLOB column.
- PA_USER_PREFERENCE has column PREFERENCES which is of BLOB datatype. This does not mean you can’t query columns having CLOB/BLOB datatype.
By default you can’t simply query tables with CLOB/BLOB datatype in PRD. For CLOB and BLOB we have to follow the below steps for it to work. It is not workaround it is the nature of the reporting with CLOB and BLOB fields
- Use this query to return values from CLOB/BLOB columns. Just have to change the column and table name:
SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(PREFERENCES,4000,1)) AS PREFERENCES
- Alternatively, if you are searching for a specific value say a user who has the Chinese locale, you can use this query:
SELECT * FROM pa_user_preference
WHERE dbms_lob.instr(PREFERENCES, utl_raw.CAST_TO_RAW('<locale>Chinese</locale>'), 1, 1) > 0;
In the above query, you are searching for a specific string '<locale>Chinese</locale>' within the BLOB column PREFERENCES.
Note: To find what columns are having CLOB/BLOB datatype, please refer to the Data Dictionary.
PRD; CLOB; BLOB; dbms_lob.instr; utl_raw.cast_to_varchar2; BLOB column, Retrieve, BLOB data type , KBA , LOD-SF-LMS-REP , Reporting Data , LOD-SF-LMS-PRD , Plateau Report Designer , How To