Row-Wise Initialization

Row-Wise Initialization

You can use the row-wise initialization option to create session variables dynamically and set their values when a session begins. The names and values of the session variables reside in an external database that you access through a connection pool. The variables receive their values from the initialization string that you type in the Initialization Block dialog.

For example, suppose you want to create session variables using values contained in a table named RW_SESSION_VARS. The table contains three columns:

• USERID, containing values that represent the unique identifiers of the users
• NAME, containing values that represent session variable names
• VALUE, containing values that represent session variable values

Sample Session Variables Database Table

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

To use row-wise initialization, create an initialization block and select the Row-wise initialization option. For this example, you would provide the following SQL statement for the initialization string:

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

Note that NQ_SESSION.USERID has already been initialized using another initialization block.

The following session variables would be created:

• When John connects to the Oracle BI Server, his session contains two session variables from row-wise initialization: LEVEL, containing the value 4, and STATUS, containing the value FULL_TIME.
• When Jane connects to the Oracle BI Server, her session contains three session variables from row-wise initialization: LEVEL, containing the value 8; STATUS, containing the value FULL-TIME; andGRADE, containing the value AAA.

Initializing a Variable with a List of Values

You can also use the row-wise initialization option to initialize a variable with a list of values. You can then use the SQL IN operator to test for values in a specified list.

For example, using the table values in the previous example, you would type the following SQL statement for the initialization string:

SELECT 'LIST_OF_USERS', USERID
FROM RW_SESSION_VARS
WHERE NAME='STATUS' AND VALUE='FULL-TIME'

This SQL statement populates the variable LIST_OF_USERS with a list, separated by colons, of the values JOHN and JANE (for example, JOHN:JANE). You can then use this variable in a filter, as shown in the following WHERE clause:

WHERE TABLE.USER_NAME = valueof(NQ_SESSION.LIST_OF_USERS)

The variable LIST_OF_USERS contains a list of values, that is, one or more values. This logical WHERE clause expands into a physical IN clause, as shown in the following statement:

WHERE TABLE.USER_NAME IN ('JOHN', 'JANE')

Note : This information is from Oracle documentation, Thanks to Oracle!!

-Asifkhan P.