NOTE Datatype in OBIEE 10g

As entire world is ready to embrace Iphone 6, i am still stuck with Iphone 3s and as fate would have it OBIEE 10g. For those who have moved on with OBIEE upgrade there will be new challenges but currently we were hand-tied-in-hole sort of issue. It was not known fact to me that OBIEE 10g doesn't support columns with data type NOTE from siebel. So when we decided to import S_NOTE_SR table in OBIEE rpd we were in for surprise when dashboad reports bombed where we had to display NOTE column. NOTE column came perfectly into OBIEE rpd with type LONGVARCHAR and size as 32768 characters but when it was used in queries it was failing with error as below:

"State: HY000. Code: 25. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 17001] Oracle Error code: 1722, message: ORA-01722: at OCI call OCIStmtFetch. [nQSError: 17012] Bulk fetch failed. (HY000"

Friends at Oracle were not kind in reponse when they told LONG datatype is not supported in OBIEE 10g. To do overnight upgrade was bigger challenge then problem at hand. Another option was to convert NOTE column into VarChar Column and bring it into OBIEE repository. Then friend-colleague-DBA-
Mastermind, Mr Shuaib, came to rescue when he was able to write function which converted NOTE datatype in VarChar and we created database View in SIEBEL schema to store curtailed-Notes.

Function: 
create or replace function f_long2Char (r rowid) return varchar2 is
  v varchar2(4000);
begin
    SELECT NOTE INTO V FROM SIEBEL.S_NOTE_SR WHERE ROWID=R;
      return substr(v,1,4000);
END;

View:
CREATE OR REPLACE VIEW VNOTE AS
SELECT ROW_ID, SRC_ROW_ID, f_long2Char(ROWID) AS NEWNOTE FROM SIEBEL.S_NOTE_SR;

Once db view was created things become simple. We were able to import this view into OBIEE rpd and reports started working like charm. However the catch here is in our case we knew the maximum length of data going into LONG column is upto 4000 characters. I might get Iphone 6 soon and upgrade sooner but if you are on OBIEE 10g and facing such issue. This might just help you.  Sometimes big is not that beautiful.

Happy Reporting!!