Dear PL/SQL experts,
I'm encountering this problem when executing below procedure, how this can be surpassed? Procedure was executing fine for last few weeks and we have this problem all of sudden :
ORA-01422: exact fetch returns more than requested number of rows
CREATE OR REPLACE PROCEDURE DBA_MAINT.SQL_EXEC_DRILLDOWN(
beginsnapid IN number,
endsnapid IN number
)
AS
begin_date varchar2(30);
end_date varchar2(30);
statname VARCHAR2(64);
err_msg VARCHAR2(100);
cnt number;
cursor c1
is
select stat_name,
sum(COUNT) as cnt
from (
select instance_number,
stat_name,
(max(VALUE) - min(VALUE)) COUNT
from dba_hist_sysstat
where stat_name in ('execute count', 'parse count (total)', 'parse count (hard)')
and snap_id between beginsnapid
and endsnapid
group by instance_number, stat_name)
group by stat_name
union all
select stat_name,
sum(COUNT) as cnt
from (
select instance_number,
stat_name,
ROUND((max(VALUE) - min(VALUE)) / 1000000) COUNT
from DBA_HIST_SYS_TIME_MODEL
where stat_name in ('DB CPU', 'background cpu time', 'sql execute elapsed time', 'parse time elapsed', 'hard parse elapsed time')
and snap_id between beginsnapid
and endsnapid
group by instance_number, stat_name)
group by stat_name union all
select stat_name,
sum(VALUE) as cnt
from (
select a.stat_name stat_name,
(b.value - a.value ) VALUE
from dba_hist_sysstat a, dba_hist_sysstat b
where a.dbid = b.dbid
and a.instance_number = b.instance_number
and a.stat_id = b.stat_id
and a.snap_id = beginsnapid
and b.snap_id = endsnapid
and a.stat_name in ('physical reads',
'physical writes',
'physical read total IO requests',
'physical write total IO requests',
'physical read total multi block requests',
'physical write total multi block requests',
'session logical reads')
and a.instance_number = b.instance_number)
group by stat_name;
begin
begin
select distinct TO_CHAR(END_INTERVAL_TIME, 'MM/DD/YYYY HH24:MI')
into begin_date
from dba_hist_snapshot
where snap_id = beginsnapid;
select distinct TO_CHAR(END_INTERVAL_TIME, 'MM/DD/YYYY HH24:MI')
into end_date
from dba_hist_snapshot
where snap_id = endsnapid;
-- DBMS_OUTPUT.PUT_LINE(begin_date);
-- DBMS_OUTPUT.PUT_LINE(end_date);
exception
when no_data_found
then
null;
end;
FOR c1_rec IN c1
LOOP
begin
insert
into DBA_MAINT.DB_SQL_EXEC_DETAILS values(
beginsnapid,
endsnapid,
begin_date,
end_date,
c1_rec.stat_name,
c1_rec.cnt
);
-- DBMS_OUTPUT.PUT_LINE(c1_rec.stat_name||' '||c1_rec.cnt);
exception
when no_data_found
then
null;
end;
end loop;
commit;
end;
/