Requirement to Close Cursors in order to refresh prompts or Option to bypass Presentation services

Alexandre Antonelli

Organization Name

AXA France GIE

Description

Hi,

 

We are currently working on a dashboard, which have several prompts.
the process is :

Step 1 : The user select a value on a first prompt,
Step 2: Some other prompts are displayed,
Step 3 : The user writes values on the displayed prompts (in order to save parameters) and these parameters are sent to the Oracle DB using a Direct SQL DB query.

Step 4: Then, once the paramaters are saved in the DB, the user clicks on a refresh button which re-run the dashboard.

Step 5: By selecting the first prompt value, the others prompts are displayed, and the prompts should show the default values which correspond to the saved paramaters done in step 3.
is retrieved depending on the first prompt value which has been sent.

=> The issue we are facing, is that when the prompt query has been executed once and we re-select the same value as before. the BIServer see that's a SQLQuery already have been sent, and retrieve the value from the Session Cursor (which is empty the first time) instead of re-executing the query...

We already add a timestamp clause in the SQL, but without success...

and If we select another value in the first prompt, and then re-select the original value, it's working fine...

=> In fact, after study, we really need to close cursor to correct this error, or force that the prompt query is executed every time, how can we proceed ?

 

please refer to the SR 3-25691916321 : Requirement to Close Cursors in order to refresh prompts, there is a record showing the issue & the SQL Query used in the prompts.

Use Case and Business Need

It's a business requirement to save prompts parameters historized in a database & then retrieve them in prompt default Value.

More details

Even if we add a timestamp clause in the SQL query of the default prompt value in order to enforce the refresh, the prompt default value isn't refreshed correctly... it seems to pass by the session cursor even if the timestamp have changed.

please refer to the SR to see a video showing the issue.

Below the updated prompt sql query which isn't not working...

SET VARIABLE DISABLE_CACHE_HIT=1,OBIS_REFRESH_CACHE=1,DISABLE_PLAN_CACHE_HIT=1;
SELECT "PARAMETER_VALUES_IN_ROWS"."VAR_CLAUSE_LIBELLE" FROM "INST" WHERE "PARAMETER_VALUES_IN_ROWS"."VAR_CONTRAT" = '@{VAR_Contrat}'
AND "PARAMETER_VALUES_IN_ROWS"."VAR_CONTRAT_ALIAS_LISTE" = '@{VAR_Contrat_Alias_Liste}'
AND "PARAMETER_VALUES_IN_ROWS"."VAR_VISION" = '@{VAR_VISION}'
AND "PARAMETER_VALUES_IN_ROWS"."VAR_TYPE_PARAM" = '@{VAR_Type_Param}'
AND "PARAMETER_VALUES_IN_ROWS"."USERNAME" = VALUEOF(NQ_SESSION.USER)
AND CURRENT_TIMESTAMP(7) > timestampadd(SQL_TSI_DAY,-365,CURRENT_TIMESTAMP)

Original Idea Number: 3b0535a681

Michal Zima

We are facing similar issue - beeing handled by following SR by Oracle support: SR 3-24997898461 : Dashboard prompt default value (Logical SQL/repository variable) is not refreshing during session.
For us is pretty critical to have it solved.