Metalink Note 304192.1 and contexts

Update: This metalink note has now been discontinued. Note 282249.1 gives information on how to pass parameter to a folder using the session client_info. The client_info is a database context available to all sessions in the USERENV namespace. Advantages of using client_info instead of a custom context is that you don't need to create the new context namespace at the database level and also the value of the client_info context can be seen in the V$SESSION dynamic view. However, it can only be used for one parameter and the client_info is used by many applications including the e-Business Suite so you run the risk of overwriting data already stored in the client_info.

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:

WHERE ENAME=SETPARAM.GET_PARAM1

Then use another condition in the workbook to set the parameter. The function always returns 1 so is always true.

1=SET_PARAM1(:"Parameter 1")

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
USING EUL_US.DISCO_PKG
/

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
AS
FUNCTION set_context(p_name VARCHAR2,
p_value VARCHAR2) RETURN VARCHAR2;
FUNCTION show_context(p_name VARCHAR2) RETURN VARCHAR2;
END disco_pkg;
/

CREATE OR REPLACE PACKAGE BODY EUL_US.DISCO_PKG
AS
FUNCTION set_context(p_name VARCHAR2,
p_value VARCHAR2) RETURN VARCHAR2
IS
BEGIN
dbms_session.set_context('DISCO_CONTEXT', p_name, p_value);
RETURN p_value;
END set_context;

FUNCTION show_context(p_name VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN SYS_CONTEXT('DISCO_CONTEXT', p_name);
END show_context;

END disco_pkg;
/

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.

4. Remove the contents from the folder so that the folder just contains a parameter.
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:
TO_DATE(SYS_CONTEXT('DISCO_CONTEXT','EFFECTIVE_DATE'))

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.