The screen shot if from prod env  and the query in the init block is very simple

select

trunc(sysdate)-1,         

trunc((sysdate)-1,'MM'),          

trunc((sysdate-1),'YY'),

decode(to_char(sysdate,'dy'),'sun',trunc(sysdate-2),'mon',trunc(sysdate-3),trunc(sysdate-1))

from dual

When I run direct database request query from answers I am able to see that query mentioned in the init block are running fine.

Last week only we migrated our 11g env to 12c. We migrated repository and catalog through  migration script. In 11g these variables were working fine but when moved in 12c they  created this problem and not being refreshed automatically.

Am able to run the queries behind these variables in the init block through data direct request from answers so it implies that queries are connections are fine .

Also I am able to run reports through the same connection detail so that also implies that connection detail are fine.

In log also  obis1-diagnostic-1.log not finding anything like initialization  block failed or any similar msg.

Thanks Gianni, Carsten ......

I got the prod RPD offline and able to verify options there .

1. It's enabled

2. Queries looks fine as same queries I  tried running them from direct data base request and am able to see data from answers so no problem with queries.

3. It's prod env rpd so am not able to query tables from the same connection pool to which init block is linked. It's Firewall which restricts.

4. It's offline RPD and again due to firewall not able to test init block queries from admin tool.

5. No online rpd so not able to manage -->sessions  to verify variables values.

Thanks Carsten for pointing to the link. I went through the link and seems similar problem you faced as well.

But of course as you pointed out that disabling the cache will clear the old cache of reports only. Hardly it's going to effect variable's value. Variable's value should be initialized to new one as per it's frequency.

In my case, based on values of these variable's reports have to be triggered so no option of using SET VARIABLE DISABLE_CACHE_HIT=1

Thanks Robert ..

but first not getting confidence that session variable will certainly work as it has to also pass through similar init blocks.

second thing that using session variable instead of repository variables will increase unnecessarily extra queries on the server and hence load.Obiee itself is slow so don't want to put extra stuffs on it.

Any other pointer  plz for resolving it ....

For now I have raised SR with oracle but they just referred me to some documents of 11g problems which are certainly not applicable in my case.

OBIEE 11g Dynamic Repository Variable may not Be Updated Dynamically ( Doc ID 2176875.1 ).

Other options am trying is

open the RPD online and create a dummy variable with high frequency and see if it triggers to run others as well.

Thanks