Two specific examples – DBMS_METADATA.GET_DDL and DBMS_AUTO_REPORT.REPOSITORY_DETAIL.
For the second procedure, I'll query the SQL Monitoring repository in DBA_HIST_REPORTS and get one or several saved reports for any given SQL_ID. For example:
select instance_number,report_id,period_start_time
from dba_hist_reports
where
component_name = 'sqlmonitor'
and report_name = 'main'
and period_start_time > trunc(sysdate)-&days
and key1 = '&sql_id'
order by period_start_time desc;
66078650 2023-05-02 02:29:14
66009917 2023-05-01 01:28:02
65892228 2023-04-29 02:14:33
65643703 2023-04-25 02:28:14
What I'd usually do next is run
select dbms_auto_report.report_repository_detail(rid=>66078650, type=>'active') from dual;
for one or all of those report IDs. Since I upgraded SQL Developer to 23.1.0.097, I get something like this:
What I have to do as a workaround (before I have to downgrade to a previous version if I can't get this solved) is create a temporary table for the CLOB output then saving the output into that table then view from that table:
create table mon_rpt (rptid number, rpt clob);
insert into mon_rpt values(66078650,dbms_auto_report.report_repository_detail(rid=>66078650, type=>'active'));
commit;
select * from mon_rpt;
At which point I can save that value to an HTML file and it displays fine.
I'm getting similar behavior for DBMS_METADATA, and the number of lines I get from that output is random each time I run it.
Nothing unexpected shows up in the statements when I turn on logging:
Thanks.