How to use session variable in connection script

Ned Sweeney

I have created a connection to an Oracle database, with Basic connection type. I have also created a Semantic Model using this connection. In the connection pool settings, I have defined the following "before query" connection script:

insert into test_login_log(txt) values ('VALUEOF (NQ_SESSION.USER)')

This inserts the text "VALUEOF (NQ_SESSION.USER)", rather than the value of the session variable. If I remove the single quotes, I get error "ORA-01747: invalid user.table.column, table.column, or column specification".

My intent is to store the OAC user in a database session context to allow fine grained data access.

Can someone explain how to use this session variable in a "before query" connection script?

Thank you,

Ned

Ram-Oracle

Hi ned,please try this once

Insert into test_login_log(txt) values (':USER')

Gianni Ceresa

While I didn't try recently, and definitely not in OAC, in older OBIEE the syntax VALUEOF(NQ_SESSION.USER) was used in the "Execute before query" of the connection pool.

My guess would be: is it possible that your query is executed before the user is even connected? Is your connection pool really establishing a connection only after a user connected to your OAC and is about to execute queries?

Ned Sweeney

Ram,

Thank you. That provided the value I needed.

Ned