I came across one requirement where one column having approximately 7000 char length when we trying to convert that CLOB field into varchar.But as we know oracle has limitation on varchar2 length upto 4000 chars only.
So the workaround I have approached is:
Split the 7000 char clob column into two varchar columns in RPD using DBMS_LOB.substr() function
Syntax for DBMS_LOB.substr() is:
DBMS_LOB.substr(<table.columnname>,<length of characters>,<start position>)
Column1: This brings first 3500 chars
CAST ( EVALUATE(‘DBMS_LOB.substr(%1,3500,1)’, Customers.”Customer Segment”) AS VARCHAR ( 3500 ))
Column2: This brings Next 3500 chars
CAST ( EVALUATE(‘DBMS_LOB.substr(%1,3500,3501)’, Customers.”Customer Segment”) AS VARCHAR ( 3500 ))
Now in Answers drag these two columns into criteria and one more column:
In 3rd column-> go to column properties-> data format and select Treat Text As – Custom Text Format.
And in Custom Text Format give the text liek this:@[html]@1@2
Where:
@1 and @2 are column positions in answers criteria.
In this case BI server doesn’t include the 3rd column in query to DB as we are Camouflage the concatenation of columns based on browser capabilities i.e. using HTML which will show the exactly how we do string concatenation.
Posts
