OBIEE LONG data type in Answers

OBIEE does not support Long Data Type , whenever we use some column in Answer of long data type the Query will fail & throw the below error

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 17001] Oracle Error code: 997,

message: ORA-00997: illegal use of LONG datatype at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000).

--> There is no solution for it in Answer level but what we can do is we need use the below function to convert it to Varchar2(4000) & use it .

Note : Maximum size is 4000 further increasing may not work in OBIEE.

Create or Replace Function Fn_GetLong( p_tname in varchar2,

p_cname in varchar2,

p_rowid in rowid ) return varchar2

as

l_cursor integer default dbms_sql.open_cursor;

l_n number;

l_long_val varchar2(4000);

l_long_len number;

l_buflen number := 4000;

l_curpos number := 0;

begin

dbms_sql.parse( l_cursor,

'select ' || p_cname || ' from ' || p_tname ||

' where rowid = :x',

dbms_sql.native );

dbms_sql.bind_variable( l_cursor, ':x', p_rowid );

dbms_sql.define_column_long(l_cursor, 1);

l_n := dbms_sql.execute(l_cursor);

if (dbms_sql.fetch_rows(l_cursor)>0)

then

dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,

l_long_val, l_long_len );

end if;

dbms_sql.close_cursor(l_cursor);

return l_long_val;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('some error');

end Fn_GetLong;