OBIEE 12c Row-wise Init Block Error: [nQSError: 13009] Row-wise initialization of session variables must be done with a 2 column query (Doc ID 2501091.1)

Last updated on SEPTEMBER 07, 2023

Applies to:

Symptoms

Setting up row level security in the RPD with the use of UPPER in the initialization block.
When the user id in OID is lower case and upper case in the Security table in the DB, the reports do not return data and log shows the initialized values instead.
When the user id is updated in the Security table to be lower case, then it works and returns values for the user and the report returns data.

Expect the use of UPPER in the init block to handle the issue and allow the row level security to work whether the user ID is upper or lower in OID.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Setup an init block to:
where 1=1 and UPPER('VALUEOF(NQ_SESSION.USER)') = UPPER(A.USER_GUID)

2. OID has user ID in lower and Security table has user in upper.

3. Run the report and it does not work.
4. Set the Security table to be user ID in lower case and now it works.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.