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 —
+91 7028476376