Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Getting ORA-08103 using GLOBAL TEMPORARY table in stored proc

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

860 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