Error: 'Not owner of table' when running custom SP in UCCX

Version 1
    This document was generated from CDN thread

    Created by: Finnur Eiriksson on 17-04-2013 09:28:18 AM
    I created a custom SP for a Wrapup code report in UCCX. I'm writing Wrapup codes to customvariable1-10, multiple wrapup codes per call.
    I copied 'getAgentWrapupDetail' SP and added my code to it, creating two additional temp tables to work with the data.
    Calling the SP in Server Studio works fine
    Running a report in CUIC Premium using this SP works fine
    I imported the report from CUIC Premium to CUIC running on UCCX and when I run it there I get a Database error saying:
    Nested SQLException; SQLState: IX000 Vendor code: -313 Message: Not owner of table
    CUIC Premium Version:9.1(1) build 1 using same data source as UCCX
    CUIC on UCCX Version:9.0(2) build 1
     
    Anyone have an idea what the problem could be? Does UCCX have a list of allowed temp table names that can be used? This is too weird for me.
     
    Thanks,
    Finnur

    Subject: RE: Error: 'Not owner of table' when running custom SP in UCCX
    Replied by: Finnur Eiriksson on 18-04-2013 09:59:37 AM
    Found the problem, fixed.
    CUIC on UCCX uses 'uccxhrc' as user to connect to the datasource. I was using 'uccxhruser' to create the SP and for the data source on CUIC.
    I created a new data source on CUIC on UCCX using 'uccxhruser', imported the report again and selected the new data source for the report definition. Everything works fine now.
     
    Finnur

    Subject: RE: Error: 'Not owner of table' when running custom SP in UCCX
    Replied by: Steven Pawlak on 22-10-2013 04:35:30 PM
    Finnur, 

    That has to be a bug as the uccxhruser is the one designed for us to access the database with for custom reporting. 

    How did you get the Report Definition to ask for a new Data Source? When you delete a report the Defintion stays. Uploading the same report again does not ask. 

    Subject: RE: Error: 'Not owner of table' when running custom SP in UCCX
    Replied by: Finnur Eiriksson on 23-10-2013 04:34:22 AM
    You need to change the name of the Report Definition to get the option to select a new Data Source. 

    Finnur

    Subject: RE: Error: 'Not owner of table' when running custom SP in UCCX
    Replied by: Steven Pawlak on 12-11-2013 01:55:13 PM
    Update for all that come here. 

    This is in fact a bug in the way the Informix users function as well as permissions to other temp tables. CSCul06940. Do not follow their workaround, I have one better. 

    First, some back history of what is happening. When, as the uccxhruser, you create your own Function/Procedure (F/P) in informix and you want to call upon one of Cisco's already made functions or procedures, usually of the convention "Get...", you need to make sure you DROP TABLE for each of the tables that is created in the Cisco F/P. You drop the tables because otherwise you get a Table Already Exists error message. You do this at the beginning of your F/P and at the end, just like Cisco does. 

    However, due to this issue if someone runs a canned report to calls the same Get... that your F/P is going to call, then you get this "Not Owner of Table" error when you attempt to DROP TABLE at the beginning or end of your F/P. The real work around for this is to add a comma separated -313 to the same place that has the -206 before the DROP TABLE statements. This is an exception catch and the -206 is for table does not exists. The -313 is for not owner of table. So when you attempt to drop the table and it throws the -313 error this will catch it and move on, instead of killing the run. 

    This works since all the Get... F/P, in at least 9.02 and SU1, primarily DELETE FROM their tables first and on a -206 error they try to create tables. So skipping over the dropping of these tables if a -313 is thrown is ok as they will be cleared out when you go to load them in the call.