About Dynamically Creating Session Variables and Setting Their Values

In a session variable's initialization block, you can select the Variable names and values option in the Query Returns field to create session variables dynamically and set their values when the session begins.

The names and values of the session variables reside in an external data source that you access through a connection pool. The variables receive their values from the initialization query that you provide.

Example 1 - Initialization Query With a Single Value Variable

Suppose you want to create session variables using values contained in a table named RW_SESSION_VARS. This table contains three columns:

• USERID - Contains values that represent the unique identifiers of the users.

• NAME - Contains values that represent session variable names.

• VALUE - Contains values that represent session variable values.

This table shows the example columns and their values.

USERID	NAME	VALUE
JOHN	LEVEL	4
JOHN	STATUS	FULL-TIME
JANE	LEVEL	8
JANE	STATUS	FULL-TIME
JANE	GRADE	AAA

To implement this example, create a session variable initialization block and select Variable names and values in the Query Returns field. Then in the Select Statement field, enter this initialization query:

SELECT NAME, VALUE
FROM RW_SESSION_VARS
WHERE USERID='VALUEOF(NQ_SESSION.USERID)'

NQ_SESSION.USERID is a system session variable that Oracle Analytics initializes for each user when they log on.

When initialized, this example creates the following session variables:

• When John connects to Oracle Analytics, his session contains two session variables: LEVEL containing the value 4, and STATUS containing the value FULL_TIME.

• When Jane connects to Oracle Analytics, her session contains three session variables: LEVEL containing the value 8, STATUS containing the value FULL-TIME, and GRADE containing the value AAA.

Example 2 - Initialization Query With a Multiple Value Variable

Suppose you want to create session variables using values contained in a table named RW_SESSION_VARS. This table contains three columns:

• ROLE_NAME - Contains values that represent user roles.

• NAME - Contains values that represent session variable names.

• VALUE - Contains values that represent session variable values.

This table shows the example columns and their values.

ROLE_NAME	NAME	VALUE
Role1	LEVEL	4
Role1	STATUS	FULL-TIME
Role2	GRADE	AAA

To implement this example, create a session variable initialization block and select Variable names and values in the Query Returns field. Then in the Select Statement field, enter this initialization query:

SELECT NAME, VALUE
FROM RW_SESSION_VARS
WHERE ';' || 'valueof(NQ_SESSION.ROLES)' || ';' like '%;' || ROLE_NAME || ';%'

If a user is assigned Role1 and Role 2, then valueof(NQ_SESSION.ROLES) returns the value Role1;Role2.

NQ_SESSION.ROLES is a system session variable that Oracle Analytics initializes for each user when they log on.

When initialized, this example creates the following session variables:

• When users assigned to Role1 connect to Oracle Analytics, their sessions contains two session variables: LEVEL containing the value 4, and STATUS containing the value FULL-TIME.

• When users assigned to Role2 connect to Oracle Analytics, their sessions contain one session variable GRADE containing the value AAA.

• When users assigned to Role 1 and Role2 connect to Oracle Analytics, their sessions contain three session variables: LEVEL containing the value 4, STATUS containing the value FULL-TIME, and GRADE containing the value AAA.