oracle-databaseplsqlcursorplsqldeveloperdeclare

PL/SQL Cursor declaration inside begin end block


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


Solution

  • 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;