2 Replies Latest reply: Jul 3, 2014 5:40 AM by 1050166 RSS

    Discoverer get the sys_context parameter every second time

    1050166

      Hi,

       

      I have very strange situation with Oracle BI Discoverer 11g (11.1.1.6.0)

       

      There is a package which sets in the context two values DATE_FROM and DATE_TO. In the view it looks like as:

      AND aeb.GLOB_YEAR_MONTH >= to_number(SYS_CONTEXT('SEPA','DATE_FROM'))

      AND aeb.GLOB_YEAR_MONTH <= to_number(SYS_CONTEXT('SEPA','DATE_TO')) 

       

       

      I have created workbook with worksheet, which relies to the view and uses parameters for further passing them to sys_context.

       

      The problem is, that opening worksheet for first time and entering parameters I get the empty result, but as soon as I make refresh of parameters (values remain the same) I get the results.

       

      In other words, I get the results for the query every second execution.

       

       

       

      Anyy ideas ?

       

      Thanks in advance

        • 1. Re: Discoverer get the sys_context parameter every second time
          1050166

          When I add the parameter value to the report heading I have very strange value, if I choose 201402 as input value, un the report heading I see parameter value as 201402,201401.

           

          Somehow previous parameter value gets into the new parameter. When I press refresh without changing parameter I get correct representing of values and parameters in the report heading.

          • 2. Re: Discoverer get the sys_context parameter every second time
            1050166

            Seems the problem is in that case:

             

             

             

            You can define a worksheet to set up many different session
            parameters and these parameters will be effective for the duration of the
            Discoverer session. However, the users should be made aware of an issue when
            this technique is used with Discoverer Plus and Viewer. Web-based Discoverer
            caches the results of SQL queries. When a workbook is opened, if the identical
            SQL query has already been run then the previous results are returned to the
            user. Discoverer does not detect that the context has changed and that the
            query will now bring back different results. This issue can usually be fixed by
            the user refreshing their workbook.