Could you share the screenshots for the init block you have created?
Thanks & Regards,
Please also provide the screenshots of the RPD too.
Thanks & Regards,
The screen shot if from prod env and the query in the init block is very simple
When I run direct database request query from answers I am able to see that query mentioned in the init block are running fine.
The repository variables works fine in 18.104.22.168.0, they refresh as expected ...
So it's up to you to double check the query you use, the connection pool and make sure everything is fine because you must have a little issue there somewhere.
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.
Ok, but are init blocks still setup correctly? If you open the RPD and check them are they fine? The connection pool selected is still valid? Are they based on the second connection pool of a given database? There are by default strong checks on the init blocks, like not being allowed to use a connection pool also used to access physical tables defined in the RPD etc.
So check these things, maybe it's just a checkbox to enable again or something like that.
Try to add a new dumb repository variable and a dumb init block with a select sysdate from dual; refreshed every few minutes and look if that one is refreshing as expected or not.
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
I have not experienced this on your version, but I previously used session variables to refresh instead, one it makes sure your variables really are 'current' and two it consistently works and is usually not too much of an overhead.
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.
The problem at the moment seems to be that they just don't acknowledge the existence of this bug and don't properly invest time in its analysis.
Unfortunately from our side there's not much we can do. There isn't an internal call which forces bulk refresh of respositoryvariables for example that we could use.
Also, the option of going for session variables as an alternative is...well...not really the same. Yes the variable will be refreshed upon each login but it will not be refresh while the session persists.
So for you, Onkar Nath Tiwari and Carsten Weber I can only say: escalate your Service Requests to the highest possible level - they just won't do anything if you don't. Escalation makes the SRs visible in their internal management food chain and is quite literally the only thing which will force them to have a second look...or better: to actually read the SR once.
Did you try creating a new fake one to see if it does refresh?
Because if it does ... you can invest 5 minutes to drop the existing init blocks and create new ones using the same code and pointing to the same variables. It's maybe enough. (It isn't a solution to the potential bug, just a workaround if you need it)