Discoverer Digression - Quickly Querying Columns Used in a Discoverer Worksheet

I suppose for many people out there, you probably are in awe that people are still running Discoverer in 2018.

Anyways, there is not a simple way to identify columns used in a Discoverer report.  There is a built in function, eul5_get_item_name, but it is a function and not easy to query against.

For migration planning and for the purposes of addressing risk to changes (e.g. we modify the compiled DFFs for an Oracle Applications table and we need to know what is affect and have the workbooks changed).

Here is my simple approach.

1. Create a table from the qpp stats table with the column information included.


CREATE TABLE eul5_us.eul5_document_columns
    AS
        ( SELECT
            qs_doc_owner,
            qs_doc_name,
            qs_doc_details,
            eul5_us.eul5_get_object_name(eul5_us.eul5_get_item_name(qs_id,'I') column_list
          FROM
            eul5_us.eul5_qpp_stats
          WHERE
            qs_created_date >= SYSDATE - 365
        );


2. I did not add indices to this table, but I suppose I could (this is left as an excercise for you).

3. Start querying the table.  I performed these steps in a non-production environment.  This is a snapshot and this type of inquiry probably is needed periodically until a complete migration has been done.

Here is an example of a DFF column which is changing and I need to know what reports were ran in the last year which use the columns being affected:



SELECT
    dc.*
FROM
    eul5_us.eul5_document_columns dc
WHERE
    column_list LIKE '%VEND$Authority_Number%'
    OR   column_list LIKE '%VEND$ATTRIBUTE_CATEGORY%';