FW: SQL Error (-7402): collcast() - DBA function c

Posted this question February 9th - no responses from the Informix community Can anyone help with how to drop and recreate a system stored procedure created as a dba function? The system stored procedure is used by an application. We do not want the application to have DBA access. I tried dropping the function with an id that has DBA access and the procedure/function states "no DBA" access. Thank you in advance Christine Bizukiewicz From: Christine Bizukiewicz Sent: Tuesday, February 09, 2010 12:04 PM To: ids@iiug.org Subject: SQL Error (-7402): collcast() Informix V10 System HP true 64 When the stored procedure is executed using Informix user - it runs fine. When stored procedure is executed using a application user with execute on the collcast function - It gets -7402 Looking for answers on how to resolve this issue The error code states -7402 Internal error. If this error occurs, note all circumstances and contact Technical Support at tsmail@us.ibm.com. Has anyone experienced and resolved this issue without opening a issue with IBM? Informix user 2/9/10 11:47 AM Executing statement: execute function sls_get_prod_ids(list{'AWINMM'}, 'ZASYST068');Query Executed; Results shown in Output1; Exec Time: 00:00:00.093; Rows retrieved: 1 Application user 2/9/10 11:57 AM Executing statement: execute function sls_get_prod_ids(list{'AWINMM'}, 'ZASYST068');SQL Error (-7402): collcast(): collcast: arg invalid func desc Error Position: Ln: 1 Col: 1 Thank you, Christine

Try dropping it as use 'informix'. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Feb 18, 2010 at 11:14 AM, Christine Bizukiewicz < christineb@shubertticketing.com> wrote: > Posted this question February 9th - no responses from the Informix > community > > Can anyone help with how to drop and recreate a system stored procedure > created as a dba function? > > The system stored procedure is used by an application. We do not want the > application to have DBA access. > > I tried dropping the function with an id that has DBA access and the > procedure/function states "no DBA" access. > > Thank you in advance > Christine Bizukiewicz > > From: Christine Bizukiewicz > Sent: Tuesday, February 09, 2010 12:04 PM > To: ids@iiug.org > Subject: SQL Error (-7402): collcast() > > Informix V10 > System HP true 64 > > When the stored procedure is executed using Informix user - it runs fine. > > When stored procedure is executed using a application user with execute on > the > collcast function - It gets -7402 > > Looking for answers on how to resolve this issue > > The error code states > > -7402 Internal error. > If this error occurs, note all circumstances and contact Technical Support > at > tsmail@us.ibm.com. > > Has anyone experienced and resolved this issue without opening a issue with > IBM? > > Informix user > > 2/9/10 11:47 AM Executing statement: > execute function sls_get_prod_ids(list{'AWINMM'}, 'ZASYST068');> Query Executed; Results shown in Output1; Exec Time: 00:00:00.093; Rows > retrieved: 1 > > Application user > > 2/9/10 11:57 AM Executing statement: > execute function sls_get_prod_ids(list{'AWINMM'}, 'ZASYST068');> SQL Error (-7402): collcast(): collcast: arg invalid func desc > Error Position: Ln: 1 Col: 1 > > Thank you, > > Christine > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00032555fdbe277461047fe2bb8e

Tried it with user Informix got the below message. > I tried dropping the function with an id that has DBA access and the > procedure/function states "no DBA" access. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Thursday, February 18, 2010 11:48 AM To: ids@iiug.org Subject: Re: FW: SQL Error (-7402): collcast() - DBA fu.... [19075] Try dropping it as use 'informix'. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Feb 18, 2010 at 11:14 AM, Christine Bizukiewicz < christineb@shubertticketing.com> wrote: > Posted this question February 9th - no responses from the Informix > community > > Can anyone help with how to drop and recreate a system stored procedure > created as a dba function? > > The system stored procedure is used by an application. We do not want the > application to have DBA access. > > I tried dropping the function with an id that has DBA access and the > procedure/function states "no DBA" access. > > Thank you in advance > Christine Bizukiewicz > > From: Christine Bizukiewicz > Sent: Tuesday, February 09, 2010 12:04 PM > To: ids@iiug.org > Subject: SQL Error (-7402): collcast() > > Informix V10 > System HP true 64 > > When the stored procedure is executed using Informix user - it runs fine. > > When stored procedure is executed using a application user with execute on > the > collcast function - It gets -7402 > > Looking for answers on how to resolve this issue > > The error code states > > -7402 Internal error. > If this error occurs, note all circumstances and contact Technical Support > at > tsmail@us.ibm.com. > > Has anyone experienced and resolved this issue without opening a issue with > IBM? > > Informix user > > 2/9/10 11:47 AM Executing statement: > execute function sls_get_prod_ids(list{'AWINMM'}, 'ZASYST068'); > Query Executed; Results shown in Output1; Exec Time: 00:00:00.093; Rows > retrieved: 1 > > Application user > > 2/9/10 11:57 AM Executing statement: > execute function sls_get_prod_ids(list{'AWINMM'}, 'ZASYST068'); > SQL Error (-7402): collcast(): collcast: arg invalid func desc > Error Position: Ln: 1 Col: 1 > > Thank you, > > Christine > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00032555fdbe277461047fe2bb8e ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

IDS does not allow anyone to drop any system stored procedure. So, you cannot use the workaround of dropping and recreating the function. I am not sure why the application user cannot execute collcast() as the execute privilege of this function is granted to public. I can execute the function as myself on Informix V10 server through dbaccess: execute function collcast(list{'AWINMM'}); (expression) LIST{'AWINMM'} Can you try to run the same query as your application user to check if this is actually caused by the execution privilege? Regards, Ken

Since the error message recommends contacting Tech Support, is there a reason why you've chosen not to do that? On Thu, Feb 18, 2010 at 08:14, Christine Bizukiewicz < christineb@shubertticketing.com> wrote: > Posted this question February 9th - no responses from the Informix > community > > Can anyone help with how to drop and recreate a system stored procedure > created as a dba function? > > The system stored procedure is used by an application. We do not want the > application to have DBA access. > > I tried dropping the function with an id that has DBA access and the > procedure/function states "no DBA" access. > > Thank you in advance > Christine Bizukiewicz > > From: Christine Bizukiewicz > Sent: Tuesday, February 09, 2010 12:04 PM > To: ids@iiug.org > Subject: SQL Error (-7402): collcast() > > Informix V10 > System HP true 64 > > When the stored procedure is executed using Informix user - it runs fine. > > When stored procedure is executed using a application user with execute on > the > collcast function - It gets -7402 > > Looking for answers on how to resolve this issue > > The error code states > > -7402 Internal error. > If this error occurs, note all circumstances and contact Technical Support > at > tsmail@us.ibm.com. > > Has anyone experienced and resolved this issue without opening a issue with > IBM? > > Informix user > > 2/9/10 11:47 AM Executing statement: > execute function sls_get_prod_ids(list{'AWINMM'}, 'ZASYST068');> Query Executed; Results shown in Output1; Exec Time: 00:00:00.093; Rows > retrieved: 1 > > Application user > > 2/9/10 11:57 AM Executing statement: > execute function sls_get_prod_ids(list{'AWINMM'}, 'ZASYST068');> SQL Error (-7402): collcast(): collcast: arg invalid func desc > Error Position: Ln: 1 Col: 1 > > Thank you, > > Christine > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even. Charles de Gaulle<http://www.brainyquote.com/quotes/authors/c/charles_de_gaulle.html>; - "The better I get to know men, the more I find myself loving dogs." --000e0cd1ab0678d3cd048017833c