Solved

Getting ORA-08103 using GLOBAL TEMPORARY table in stored proc

Posted on 2008-06-18
5
1,954 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.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now