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
|
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
|
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
|
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 |
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
|
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 |
Thanks – Shivmohan Purohit —
@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.
LikeLike
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”
LikeLike