?
Solved

Getting ORA-08103 using GLOBAL TEMPORARY table in stored proc

Posted on 2008-06-18
5
Medium Priority
?
2,010 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
[X]
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
  • 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 1500 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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

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‚Ķ
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

762 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