Solved

Getting ORA-08103 using GLOBAL TEMPORARY table in stored proc

Posted on 2008-06-18
5
1,972 Views
Last Modified: 2013-12-18
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;

Open in new window

0
Comment
Question by:kbslpdev2
  • 3
  • 2
5 Comments
 
LVL 22

Expert Comment

by:DrSQL
ID: 21815059
It's a binding issue.  The stored procedure can't bind to an object that isn't permanent.  Normally, it would create the temp table (but it still can't declare a ref cursor against that object - you have to use dynamic sql). Youucould use a permanent table and just make sure you do a rollback after you're done.  It shouldn't HAVE to be a temp table.

Good luck!
0
 

Author Comment

by:kbslpdev2
ID: 21815393
Thanks, that makes sense.

Will it affect the contents of the cursor to do a rollback before the fetch is finished?
0
 
LVL 22

Accepted Solution

by:
DrSQL earned 500 total points
ID: 21815629
Yes, it could.  I don't know the architecture you're working with.  I was assuming that the application that reads the REF Cursor is part of the same session that inserts the "split" data.  That's the only way you can have the inserts be temporary - no other session can read them until they are committed and they go away if the session ends.  So, just don't commit (or rollback) until the data has been read.  Can you make that (rollback or delete) another procedure that runs when the report finishes?

But, if the "SRSS" application has its own login and isn't calling the procedure directly, then you'll have to commit the records before you pass them on.  Your procedure could always delete (or truncate) all of the records in the "temporary" table first, then do the split insert, commit, and pass the ref cursor.  Then the data would stick around until the next time you ran the procedureor deleted it yourself (like with another procedure at the end of the report).  This approach also has flaws if more than one user might be running the function at a time.  You'd need to add a sequence number or a session id to the records you are creating to have a multiuser solution.

I should probably know what an SSRS report is, but it's not ringing any bells.   Sorry if my terminology doesn't fit.

Good luck!
0
 

Author Comment

by:kbslpdev2
ID: 21816323
SSRS is SQL Services Reporting Services.

I will play around with these ideas and see what works best.

Thanks again for your help.
0
 

Author Comment

by:kbslpdev2
ID: 21817005
I mean SQL Server Reporting Services.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question