Oracle: Subqueries in Discoverer

With no out-of-the box method of using dynamic parameters in SQL queries within Discoverer, reports can become very cumbersome and costly to run. However, there is a work around for this with the use of database context values.

In this example we will create a package to set a context value that can then be referenced within a custom Discoverer workbook.

The first step is to create the custom context and a package that will set and return the context value (in this example xxapps_eul_us is the database user owning the Discoverer EUL):


CREATE OR REPLACE CONTEXT EUL_DISCO USING xxapps_eul_disco
/

CREATE OR REPLACE PACKAGE xxapps_eul_disco AUTHID DEFINER AS

FUNCTION set_context(p_name VARCHAR2
,p_value VARCHAR2)
RETURN VARCHAR2;

FUNCTION show_context(p_name VARCHAR2)
RETURN VARCHAR2;

END xxapps_eul_disco;
/

CREATE OR REPLACE PACKAGE BODY xxapps_eul_disco AS

FUNCTION set_context(p_name VARCHAR2
,p_value VARCHAR2) RETURN VARCHAR2 AS
BEGIN
dbms_session.set_context('EUL_DISCO', p_name, p_value);
RETURN p_value;
END set_context;

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

END xxapps_eul_disco;
/

GRANT ALL ON xxapps_eul_disco TO xxapps_eul_us;



Within Discoverer Administrator select Tools > Register PL/SQL Functions and create a new function:

Function Name: SET_CONTEXT
Display Name: XXAPPS Set Context
Owner: APPS
Package: XXAPPS_EUL_DISCO
Database Link:
Return Type: Varchar
Description: Set Context
Identifier: SET_CONTEXT
Available in Desktop/Plus: True

In this example the context is set based on the value entered as a parameter in the workbook (the parameter is named ‘As of date’). To set the context; create a workbook Condition referencing the XXAPPS Set Context function:

Item: ‘DUMMY’
Condition: <>
Values: =XXAPPS Set Context(‘EFFECTIVE DATE’,:“As of date”)

Now, within the SQL of the workbook the context value can be referenced as a subquery, EG:


SELECT ...

FROM ...

WHERE date <= TO_DATE(xxapps_eul_disco.show_context('EFFECTIVE DATE'),'DD-MON-RRRR')