PL/SQL Cursor declaration inside begin end block

Question

I'm working on a PL/SQL script in Oracle. I need to declare a cursor that works on a DBA_HIST_* table ONLY if the Diagnostic Pack is lincensed. I mean if:

select sum(detected_usages)
from DBA_FEATURE_USAGE_STATISTICS where name in ('ADDM','AWR Baseline','AWR Baseline Template','AWR Report','Automatic Workload Repository','Baseline Adaptive Thresholds','Baseline Static Computations','Diagnostic Pack','EM Performance Page');

is not > 0

I have to declare something like (simplified):

DECLARE
CURSOR main_metrics_cursor IS
with main_metrics as 
(
    select snap_id, end_time, round(average/100,2) cpu_per_s
    from(
            --Si recuperano le metriche necessarie filtrando su dbid e considerando solo gli snap_id recuperati precedentemente
            select snap_id, end_time, instance_number, metric_name, average, maxval
            from DBA_HIST_sysmetric_summary
            and metric_name in ('CPU Usage Per Sec')
        )
    group by snap_id, end_time
    order by snap_id, end_time
)
select * from main_metrics;
metric_row main_metrics_cursor%ROWTYPE;

The problem is that only with the declare (without the open cursor phase) Oracle knows that I used the dba_hist view.

I need that this declaration to be dependent on first query result (if the component is licensed) otherwise I need to execute another piace of code, but I can only use the if inside a begin/end block and the declaration has to be done before.

Any ideas?

I need that the DECLARE block is dependent of an if condition

Answer 1

It sounds like you don't want to declare a static cursor, you want to use dynamic SQL to open a cursor based on a dynamic SQL statement based on the result of your query

declare
  l_rc sys_refcursor;
  l_cnt integer;
begin
  select sum(detected_usages) 
    into l_cnt 
    from DBA_FEATURE_USAGE_STATISTICS 
  where name in ('ADDM','AWR Baseline','AWR Baseline Template',
                 'AWR Report','Automatic Workload Repository',
                 'Baseline Adaptive Thresholds',
                 'Baseline Static Computations',
                 'Diagnostic Pack',
                 'EM Performance Page');

  if( l_cnt > 0 )
  then 
    open l_rc for 'with main_metrics ...';
 
    <<do something with l_rc>>
  end if;
end;