cancel
Showing results for 
Search instead for 
Did you mean: 

Blob/Clob size limitation in 4.1 IDT

Former Member
0 Kudos

I have a database field in 3.1 Universe Designer that is a Long Text data type and parses and displays as expected in the Webi report.

In 4.1 IDT this same field is read as a Blob data type, and does not parse and is not available in Webi. I referred to SAP DOC-42781 that covers Blob/Clob and changed the SQL to "Dbms_lob.substr(Table.CLOB_Column , number of characters, start position)". However, there seems to be a character limit when using this SQL. I initially set my  number of characters at 14,000 (my largest record thus far is 13,268), but this produced a IDT query error "ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1". Again following SAP DOC-42781, I kept reducing the number of characters in the SQL until the error disappeared. Ultimately I determined that it caps out at 4,000 characters. This SQL works for most of my records, but a few are larger than this and as previously mentioned the largest right now is over 13,000. Thus some records are truncated and all text is not displayed as it should be. Given the Dynamic nature of the field, this SQL does not work on surface if there is such a small maximum character limit.

I've tried a couple of workarounds, but the only way I can get the field to display correctly at the report level is to create several similar objects, each extracting 4,000 characters of the string. So in my case I have 4 objects allowing for a character length of 16,000. These objects can't be combined/concatenated at the IDT level because I get an error message "ORA-01489: result of string concatenation is too long". Thus all 4 objects have to be included in any query, and I then have to create a Webi report variable to concatenate the 4 objects.

This obviously is not a practical or efficient solution. Also given the Dynamic field lengths, it is not a full proof solution either.

As previously mentioned, this was not an issue in 3.1, but is an issue in 4.0/4.1 and SAP DOC-42781 does not fully cover the size limitations.

Is there any viable solution for this within IDT?

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

I had the same issue.

Followed (as you) the guide:

In the data source the CLOB field has Data_Length 4000.

But you have to keep in mind how many space one character needs. One bit or more?

I reduce the length to 2k and was happy: dbms_lob.substr(abc.def, 2000, 1)