Dear Gurus,
I am getting extra data that should not be returned.
I am checking if i delete some records from the dense filled collection recs [Line No. 46-47] then at [Line No. 60] dbms_output should print only deleted indexs. but i am getting all the indexes which were added from recursive query.
But if I uncomment Line No. 13 then Correct Result returns.
Database: 12c
OS: Windows 8.1
Tables Used:
CREATE TABLE CALENDAR_YEAR
(
ID DATE NOT NULL
, DAYPART NUMBER(2, 0) NOT NULL
, MONTHPART NUMBER(2, 0) NOT NULL
, YEARPART NUMBER(4, 0) NOT NULL
, WEEKDAY VARCHAR2(3 BYTE) NOT NULL
, MONTHNAME VARCHAR2(3 BYTE) NOT NULL
, IS_SUNDAY NUMBER(1, 0) NOT NULL
, IS_HOLIDAY NUMBER(1, 0) NOT NULL
, CLIENT_ID NUMBER(18, 0)
, CONSTRAINT CALENDAR_YEAR_PK PRIMARY KEY
(
ID
)
);
HERE IS MY CODE:
declare
v\_sdate date := to\_date ( '01-Jul-2016' ) ;
v\_edate date := to\_date ( '10-Jul-2016' ) ;
v\_clientid number(10) := 10;
c sys\_refcursor;
type ctype is table of calendar\_year%rowtype;
recs ctype;
bulk\_errors exception;
pragma exception\_init ( bulk\_errors, - 24381 ) ;
begin
--delete from calendar_year where client_id=v_clientid;
with tx ( dt ) as
(select v\_sdate
from dual
union all
select dt + 1
from tx
where dt + 1 \<= v\_edate
)
select dt
, extract ( day from dt )
, extract ( month from dt )
, extract ( year from dt )
, to\_char ( dt, 'DY' )
, to\_char ( dt, 'Mon' )
, case
when to\_char ( dt, 'DY' ) = 'SUN'
then 1
else 0
end
, case
when to\_char ( dt, 'DY' ) = 'SUN'
then 1
else 0
end
, v\_clientid
bulk collect
into recs
from tx;
recs.delete ( 3 ) ;
recs.delete ( 7 ) ;
forall i in 1..recs.count save exceptions --indices of recs
insert into calendar_year
values recs(i) ;
exception
when bulk_errors then
begin
for idx in 1.. sql%bulk\_exceptions.count
loop
sys.dbms\_output.put\_line(
sql%bulk\_exceptions ( idx ) .error\_index
--Sql%Bulk\_Exceptions ( idx ) .Error\_Code
);
end loop;
end;
commit;
--rollback;
open c for select \* from calendar\_year;
sys.dbms\_sql.return\_result(c);
end;
DBMS OutPut RESULT: [From Line No. 60]
