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