OBIEE LOGLEVEL session variable problem

If you read my post External table authentication and row-wise initialization in OBIEE , in Part1 - External table authentication I'm using external table for authentication of the user, group, displayname and loglevel.

I'm using b_ext_tab_auth_all block:

select username, groupname, displayname, loglevel
from HR.OBIEE_EXT_TAB_AUTH
where username=':USER'
and password=':PASSWORD'

The table OBIEE_EXT_TAB_AUTH has loglevel column number datatype in the database and when you import it to a repository it has double datatype:



Logon with some user (UserC, which has loglevel 5 in the database) and make a simple request and see NQQuery.log.

You'll see that the log in the NQQuery.log does not exist.

Try to put first in the column in the report VALUEOF(NQ_SESSION.LOGLEVEL) to see what is populated:



Ok, this is because decimal is populated and LOGLEVEL need to have integer as input to work properly.

Solution:

Leave datatype of imported table to DOUBLE and do a explicit TO_NUMBER(loglevel) conversion:

select username, groupname, displayname, to_number(loglevel)
from HR.OBIEE_EXT_TAB_AUTH
where username=':USER'
and password=':PASSWORD'

Now it works and you'll get log in the NQQuery.log.

*Note
If you just change datatype of imported table from DOUBLE to INT and leave block without change:

select username, groupname, displayname, loglevel
from HR.OBIEE_EXT_TAB_AUTH
where username=':USER'
and password=':PASSWORD'

it does not work, you can't see logs in NQQuery.log.

If you are using character for LOGLEVEL column in the database then you need to put TO_NUMBER(loglevel) also.