Solved

Getting ORA-08103 using GLOBAL TEMPORARY table in stored proc

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Workhours Count beetween Workhours 3 40
Need SQL Query to Find Foreign-keys Without Indexed Columns 4 33
format dd/mm/yyyy parameter 16 49
Oracle Errors 11 52
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

726 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