I have followed the steps mentioned in the link http://www.rittmanmead.com/2010/08/oracle-bi-ee-11g-reporting-on-clobs-lookups/.
Still I get the following error:
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 17001] Oracle Error code: 932, message: ORA-00932: inconsistent datatypes: expected - got CLOB at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)
I do not want to cast the column formula and then use in the report. I want to directly use the column from Presentation layer and directly report on it.
If possible create a view in database by selecting all the columns by name and use this DBMS_LOB.SUBSTR(<Clob column>) for clob column.
ex: create view as select DBMS_LOB.SUBSTR(col1), col2 clo3 from table
Once you done with this just rename table in presentation to view name, that should work.
for the modified clob column you may set varchar2(4000) or more