awr_report_via_ords.sql

DECLARE
  l_roles owa.vc_arr;
  l_patterns owa.vc_arr;
  l_modules owa.vc_arr;

BEGIN  
  l_roles(1)   := 'SQL Developer';
  l_patterns(1):= '/dba/*';
  l_modules(1) := 'dba/awr';


  
  ORDS.DEFINE_MODULE(
      p_module_name    => 'dba/awr',
      p_base_path      => '/dba/perf/awr/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'dba/awr',
      p_pattern        => '/snapshot',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'dba/awr',
      p_pattern        => '/snapshot',
      p_method         => 'GET',
      p_source_type    => 'json/collection',
      p_items_per_page => 25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
q'[select 
   SNAP_ID,DBID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME , 
   '../awr/report/'||DBID||'/1/'||(SNAP_ID-1)||'/'||SNAP_ID "$report-1",
   '../awr/report/'||DBID||'/1/'||(SNAP_ID-2)||'/'||SNAP_ID "$report-2",
   '../awr/report/'||DBID||'/1/'||(SNAP_ID-3)||'/'||SNAP_ID "$report-3",
   '../awr/report/'||DBID||'/1/'||(SNAP_ID-4)||'/'||SNAP_ID "$report-4",
   '../awr/report/'||DBID||'/1/'||(SNAP_ID-5)||'/'||SNAP_ID "$report-5"
from dba_hist_snapshot 
where INSTANCE_NUMBER=1 order by SNAP_ID desc]');

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'dba/awr',
      p_pattern        => '/snapshot/:id',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'dba/awr',
      p_pattern        => '/snapshot/:id',
      p_method         => 'GET',
      p_source_type    => 'json/collection',
      p_items_per_page => 25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         =>  'select * from dba_hist_snapshot where SNAP_ID = :id');

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'dba/awr',
      p_pattern        => 'report/:dbid/:inst/:snapbegin/:snapend',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'dba/awr',
      p_pattern        => 'report/:dbid/:inst/:snapbegin/:snapend',
      p_method         => 'GET',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'begin
FOR cur_rep IN (SELECT output
                      FROM   
                      TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(:dbid,:inst,:snapbegin,:snapend )))
      LOOP
        htp.prn(cur_rep.output);
      END LOOP;
end;');

  ORDS.DEFINE_PRIVILEGE(
      p_privilege_name => 'dba.demo',
      p_roles          => l_roles,
      p_patterns       => l_patterns,
      p_modules        => l_modules,
      p_label          => '',
      p_description    => 'protected by SQL Developer Role',
      p_comments       => NULL);      

        
COMMIT;

END;
/