This metalink note describes how to pass parameters into a custom folder or database view using stored procedures. The note has been around for quite a while, but as I will explain, the method used will never work reliably in all situations. The note suggests that you use two functions held in a package to set and get a parameter. You use a condition in the view or custom folder to retrieve the parameter, for example:
Then use another condition in the workbook to set the parameter. The function always returns 1 so is always true.
This approach expects that the SET_PARAM1 function will be called first because it uses constants and so can be evaluated without referencing any tables. However, this will only work if the query in the view or custom folder is not too complex. Once an SQL statement has been sent to the database you have no control over the order in which stored procedures are called. With complex SQL statements, for example, using CONNECT BY, the GET_PARAM1 function will be called first and will return either no value or the last value used.
There is NO reliable way of passing parameters from Discoverer into a custom folder or view using a single SQL statement because the database can call the stored procedures in any order. So any parameter added to the query by Discoverer will probably not be processed before the main body of the query held in a custom folder or view.
Database contexts provide a more efficient and reliable alternative. Database contexts work like session parameters, once the context is set it is available to use for the duration of the session. You can set them or change them using a separate Discoverer worksheet.
To implement Discoverer session parameters you will first have to create a context namespace in the database for the Discoverer contexts.
CREATE OR REPLACE CONTEXT DISCO_CONTEXT
Then you need to create a package to enable you to set and retrieve contexts from in Discoverer.
CREATE OR REPLACE PACKAGE EUL_US.DISCO_PKG
FUNCTION set_context(p_name VARCHAR2,
p_value VARCHAR2) RETURN VARCHAR2;
FUNCTION show_context(p_name VARCHAR2) RETURN VARCHAR2;
CREATE OR REPLACE PACKAGE BODY EUL_US.DISCO_PKG
FUNCTION set_context(p_name VARCHAR2,
p_value VARCHAR2) RETURN VARCHAR2
dbms_session.set_context('DISCO_CONTEXT', p_name, p_value);
FUNCTION show_context(p_name VARCHAR2) RETURN VARCHAR2
RETURN SYS_CONTEXT('DISCO_CONTEXT', p_name);
Once you have imported the set_context and show_context functions into the Discoverer EUL you are ready to set and retrieve your session parameters.
With this approach you set your session parameters in a separate worksheet from your main reports. For example, the following steps show you how to create a worksheet to define an effective date parameter:
1. Create a workbook based on any folder containing an item of type date.
2. Create a parameter based on a date.
3. Create a calculation to call the set_context function and set the effective date.
5. It is also useful to create another calculation retrieving the value of the parameter, so that the value entered for the parameter can be seen when the workbook is run.
The value of the context in a custom folder or view can be retrieved using a condition, for example:
Every EUL of any complexity should use database contexts:
1. Using SYS_CONTEXT gives better performance. SYS_CONTEXT is an SQL function and therefore much quicker than using calls to PL/SQL.
2. SYS_CONTEXT can be used in any code; in views, PL/SQL code and custom folders. The function can also in the EUL and workbooks; you can import the function into the EUL (from the SYS schema) and use it instead of the SHOW_CONTEXT function. SYS_CONTEXT will always return the current value of the parameter for the session.
3. You can store all your parameters in the same database namespace, so there is no need to change any code if there is a need extra parameters.
4. You can set the database contexts to default values using a login trigger. Then the users only have to run the parameter worksheet if they need to change a session parameter. You can even store the last contexts used in a table, so that a new Discoverer session picks up the values used in a previous session.
However, one limitation of this technique is that Discoverer caches the results of some SQL such as the lists of values and so changing a session parameter will not change a list of values.