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.
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.
Posts
