Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

How to use session variable in connection script

Accepted answer
43
Views
3
Comments
Ned Sweeney
Ned Sweeney Rank 2 - Community Beginner
in Oracle Analytics Cloud and Server

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

Best Answer

  • Ram-Oracle
    Ram-Oracle Rank 6 - Analytics Lead
    Answer ✓

    Hi ned,please try this once

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

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    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
    Ned Sweeney Rank 2 - Community Beginner

    Ram,

    Thank you. That provided the value I needed.

    Ned