Getting ORA-08103 using GLOBAL TEMPORARY table in stored proc

Hello,

I have an SSRS report that needs to retrieve data based on a comma separated list of employee ids.  In order to change this list into something that can be filtered on I am using a custom split function to convert it into a table as such:

table(sysadm.split(in_emplid))

I can use this with an IN clause in a normal select statement but it is expensive.  Just doing the split should not be that expensive but for some reason Oracle has a hard time with it when it is part of a complex join.  

I decided to do this by writing a stored procedure that puts the data into a global temporary table  and then join on that table(see attached code).  Now I am getting an "ORA-08103: object no longer exists".  I think this has something to do with doing and insert and a fetch in the same stored proc, but I have not been able to find much on this.

Does anyone know what is causing this error?  Also, if someone has a better way of doing this that would be great.

 

CREATE GLOBAL TEMPORARY TABLE "KNEXTUSER"."EMPLID_TEMP" 
   (	"EMPLID" VARCHAR2(10 BYTE)
   ) ON COMMIT DELETE ROWS ;
 
 
create or replace PROCEDURE k_sp_vacsicot(
                    in_emplid IN VARCHAR2,
                    in_year IN VARCHAR2,
                    cur OUT SYS_REFCURSOR) AS
BEGIN
  
insert into emplid_temp select * from table(sysadm.split(in_emplid));
 
 OPEN cur FOR 
 select EMPLID, NAME, HIRE_DT, LOCATIONNAME, DEPTID, DEPTNAME, K_CAPABLTY_CD, JOBCODE, FLSA_STATUS,
sum(sick_hrs), sum(vac_hrs), sum(OT_hrs) from (
select a.EMPLID, NAME, HIRE_DT, LOCATIONNAME, DEPTID, DEPTNAME, K_CAPABLTY_CD, JOBCODE, FLSA_STATUS,
nvl(b.HRS_MTD, 0) as sick_hrs, 0 as vac_hrs, 0 as OT_hrs
from emplid_temp t
inner join sysadm.vw_employee_info_reports a on a.emplid=t.emplid
left outer join SYSADM.VW_EMPLOYEE_COMP_SCK b on (
  a.emplid=b.emplid and b.erncd in ('SCK', 'FMS', '002', '007', '102', '107', 'CSN', 'CST') and b.year=in_year)
union
select a.EMPLID, NAME, HIRE_DT, LOCATIONNAME, DEPTID, DEPTNAME, K_CAPABLTY_CD, JOBCODE, FLSA_STATUS,
0 as sick_hrs, nvl(c.HRS_MTD, 0)  as vac_hrs, 0 as OT_hrs
from emplid_temp t
inner join sysadm.vw_employee_info_reports a on a.emplid=t.emplid
left outer join SYSADM.VW_EMPLOYEE_COMP_VAC c on (
  a.emplid=c.emplid and c.erncd in ('003', '103', '4%V') and c.year=in_year) 
union
select a.EMPLID, NAME, HIRE_DT, LOCATIONNAME, DEPTID, DEPTNAME, K_CAPABLTY_CD, JOBCODE, FLSA_STATUS,
0 as sick_hrs, 0 as vac_hrs, nvl(d.HRS_MTD, 0) as OT_hrs
from emplid_temp t
inner join sysadm.vw_employee_info_reports a on a.emplid=t.emplid
left outer join SYSADM.VW_EMPLOYEE_COMP_OT d on (a.emplid=d.emplid and d.erncd LIKE 'O%' AND d.year=in_year)
)
group by EMPLID, NAME, HIRE_DT, LOCATIONNAME, DEPTID, DEPTNAME, K_CAPABLTY_CD, JOBCODE, FLSA_STATUS;
 
 
END;

Select allOpen in new window