How to Find a Particular Scheduled Workbook’s Result Set Table in Discoverer


hello discoverer friends, here a good technical insight within oracle discoverer for scheduled report, what are the tables behind and how they linked. hope you would like n find it useful. do share ur feedback to improve future articles.

 

 

How to Find a Particular Scheduled Workbook’s Result Set Table in Discoverer

1. Create a simple Discoverer workbook based on SCOTT.DEPT table, workbook name is ‘DEPT’
2. Schedule DEPT workbook as ‘SCOTT’ user
3. Wait until the scheduled job has completed; in the Scheduling Manager the report status is ‘Ready’
4. Connect to SQLPlus as the EUL owner 

5. Select BR_ID, BR_WORKBOOK_NAME from EUL5_BATCH_REPORTS where BR_WORKBOOK_NAME=’DEPT’;

 

SQL> select BR_ID, BR_WORKBOOK_NAME from EUL5_BATCH_REPORTS where BR_WORKBOOK_NAME=’DEPT’;

BR_ID      BR_WORKBOOK_NAME
——————————————————————————–
203443      DEPT

6. Select BS_ID, BS_BR_ID, BS_SHEET_NAME from EUL5_BATCH_SHEETS where BS_BR_ID=’203443‘;

SQL> select BS_ID, BS_BR_ID, BS_SHEET_NAME from EUL5_BATCH_SHEETS where BS_BR_ID=’203443′;

BS_ID      BS_BR_ID      BS_SHEET_NAME
——————————————————————————–
203444      203443             Sheet 1

 

7. Select BQ_ID, BQ_BS_ID from EUL5_BATCH_QUERIES where BQ_BS_ID=’203444‘;

select BQ_ID, BQ_BS_ID from EUL5_BATCH_QUERIES where BQ_BS_ID=’203444′;

BQ_ID      BQ_BS_ID
——————– ———
203445      203444

 

8. Select BQT_ID, BQT_BQ_ID, BQT_TABLE_NAME from EUL5_BQ_TABLES where BQT_BQ_ID=’203445‘;

select BQT_ID, BQT_BQ_ID, BQT_TABLE_NAME from EUL5_BQ_TABLES where BQT_BQ_ID=’203445′;

BQT_ID      BQT_BQ_ID      BQT_TABLE_NAME
——————————————————————————————————————
203454         203445                EUL5_B070924102545Q1R1

This shows that the scheduled workbook ‘DEPT: sheet1’ is associated with the Result Set table
‘EUL5_B070924102545Q1R1’

 

SQL> select * from EUL5_B070924102545Q1R1;

BRN1 BRVC1                BRVC2
——————————————-
10        ACCOUNTING  NEW YORK
20        RESEARCH       DALLAS
30        SALES                 CHICAGO
40        OPERATIONS   BOSTON

 

9. Information about when the table was created, the creator, last updated date and who updated it can be obtained from EUL5_BQ_TABLES;

SQL> select BQT_TABLE_NAME, BQT_CREATED_BY, BQT_CREATED_DATE from EUL5_BQ_TABLES;

BQT_TABLE_NAME                 BQT_CREATED_BY      BQT_CREATED_DATE
——————————————————————————————————————
EUL5_B070924102545Q1R1      SCOTT                              24-SEP-07

 

 

 

 Thanks – Shivmohan Purohit —

 

 

3 thoughts on “How to Find a Particular Scheduled Workbook’s Result Set Table in Discoverer

  1. @saad sheikh: Thats because you need to check which schema you are running the report from. for example, if you are running the query from APPS schema, it does not have a synonym for the above table. so you need to query “select * from EUL1_US.EUL5_B070924102545Q1R1” provided the owner of the table is “EUL1_US”. owner information you can get in the all_objects data dictionary.

    Like

  2. Hi the last query “select * from EUL5_B070924102545Q1R1;” does not work for me. Can you tell me why it gives error : “ORA-00942: table or view does not exist”

    Like

Want to give some comment to author ( Shivmohan Purohit )

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s