Unable to drop duplicate stored procedure

Hi, would like to seek for your help. I accidentally created 2 stored procedure with different parameter CREATE PROCEDURE store_reject_cancel( in_seq_no LIKE porders.seq_no, in_userid LIKE porders.userid ) RETURNING CHAR(1), VARCHAR(100); and CREATE PROCEDURE store_reject_cancel( in_seq_no LIKE porders.seq_no, in_userid LIKE porders.userid, in_desc VARCHAR(100) ) RETURNING CHAR(1), VARCHAR(100); When i try to drop the procedures, it prompt me the following error message drop command --> drop procedure store_reject_cancel Error message --> 9700: Routine (store_reject_cancel) ambiguous - more than one routine resolv So my question is how can i drop the stored procedure

Hi, You can do it by this way: drop procedure store_reject_cancel( 'datatype of the porders.seq_no','datatype of the porders.userid' ); drop procedure store_reject_cancel( 'datatype of porders.seq_no','datatype of the porders.userid', varchar(100) ); Good luck! -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of NG SOO CHEE Sent: Thursday, August 07, 2008 9:52 AM To: ids@iiug.org Subject: Unable to drop duplicate stored procedure [13005] Hi, would like to seek for your help. I accidentally created 2 stored procedure with different parameter CREATE PROCEDURE store_reject_cancel( in_seq_no LIKE porders.seq_no, in_userid LIKE porders.userid ) RETURNING CHAR(1), VARCHAR(100); and CREATE PROCEDURE store_reject_cancel( in_seq_no LIKE porders.seq_no, in_userid LIKE porders.userid, in_desc VARCHAR(100) ) RETURNING CHAR(1), VARCHAR(100); When i try to drop the procedures, it prompt me the following error message drop command --> drop procedure store_reject_cancel Error message --> 9700: Routine (store_reject_cancel) ambiguous - more than one routine resolv So my question is how can i drop the stored procedure ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi, This is a rtfm questions, however: As you have undoubtedly figured out, informix allows you to create multiple procedures with the same name. It identifies the procedure that you want to run by looking at the datatype of the parameters being passed. Similarly, in order to delete one of these procedures, you need to uniquely identify the procedure by supplying the argument datatypes for the procedure that you want to drop. Drop procedure store_reject_cancel(<data type of porders.seq_no>, <data type of porders.userid>) At this point, you will only have one store_reject_cancel sp left so you can then use the drop procedure command that you are familiar with. Regards Mark -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of NG SOO CHEE Sent: 07 August 2008 09:52 AM To: ids@iiug.org Subject: Unable to drop duplicate stored procedure [13005] Hi, would like to seek for your help. I accidentally created 2 stored procedure with different parameter CREATE PROCEDURE store_reject_cancel( in_seq_no LIKE porders.seq_no, in_userid LIKE porders.userid ) RETURNING CHAR(1), VARCHAR(100); and CREATE PROCEDURE store_reject_cancel( in_seq_no LIKE porders.seq_no, in_userid LIKE porders.userid, in_desc VARCHAR(100) ) RETURNING CHAR(1), VARCHAR(100); When i try to drop the procedures, it prompt me the following error message drop command --> drop procedure store_reject_cancel Error message --> 9700: Routine (store_reject_cancel) ambiguous - more than one routine resolv So my question is how can i drop the stored procedure **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

You may have to find the proc ids for both versions of your stored procedures and then drop all rows from sysprocplan, sysprocbody, sysprocauth and sysprocedures for that procid. Remember, sysprocedures must be done last. This has worked for me in the past. Good luck. --- On Thu, 8/7/08, NG SOO CHEE <ng.soo.chee@gmail.com> wrote: From: NG SOO CHEE <ng.soo.chee@gmail.com> Subject: Unable to drop duplicate stored procedure [13005] To: ids@iiug.org Date: Thursday, August 7, 2008, 3:52 AM Hi, would like to seek for your help. I accidentally created 2 stored procedure with different parameter CREATE PROCEDURE store_reject_cancel( in_seq_no LIKE porders.seq_no, in_userid LIKE porders.userid ) RETURNING CHAR(1), VARCHAR(100); and CREATE PROCEDURE store_reject_cancel( in_seq_no LIKE porders.seq_no, in_userid LIKE porders.userid, in_desc VARCHAR(100) ) RETURNING CHAR(1), VARCHAR(100); When i try to drop the procedures, it prompt me the following error message drop command --> drop procedure store_reject_cancel Error message --> 9700: Routine (store_reject_cancel) ambiguous - more than one routine resolv So my question is how can i drop the stored procedure ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

I don't know what the gurus will say on this, but I would be very cautious about data manipulation in the database system tables - sooner or later you will make a mistake. Better to use the tools supplied to do the job! This message is best read by implicitly inserting OTC adjectives. Regards Mark -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Walter Lowich Sent: 07 August 2008 01:10 PM To: ids@iiug.org Subject: Re: Unable to drop duplicate stored procedure [13010] You may have to find the proc ids for both versions of your stored procedures and then drop all rows from sysprocplan, sysprocbody, sysprocauth and sysprocedures for that procid. Remember, sysprocedures must be done last. This has worked for me in the past. Good luck. --- On Thu, 8/7/08, NG SOO CHEE <ng.soo.chee@gmail.com> wrote: From: NG SOO CHEE <ng.soo.chee@gmail.com> Subject: Unable to drop duplicate stored procedure [13005] To: ids@iiug.org Date: Thursday, August 7, 2008, 3:52 AM Hi, would like to seek for your help. I accidentally created 2 stored procedure with different parameter CREATE PROCEDURE store_reject_cancel( in_seq_no LIKE porders.seq_no, in_userid LIKE porders.userid ) RETURNING CHAR(1), VARCHAR(100); and CREATE PROCEDURE store_reject_cancel( in_seq_no LIKE porders.seq_no, in_userid LIKE porders.userid, in_desc VARCHAR(100) ) RETURNING CHAR(1), VARCHAR(100); When i try to drop the procedures, it prompt me the following error message drop command --> drop procedure store_reject_cancel Error message --> 9700: Routine (store_reject_cancel) ambiguous - more than one routine resolv So my question is how can i drop the stored procedure **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Hi, Informix does allow you to create more than one procedure with the same name but different parameters (signature). It's one of the OOPS concept. Even though they are the same by name but for database they are two different objects as per their signatures. In order to let database know what SP you are planning to drop, you must pass all parameter's data type. For example, Drop procedure abc (char, char, int); Note: data type listed in the ( ) must be in the same order as you have created your SP. Dharmendra > To: ids@iiug.org> From: ng.soo.chee@gmail.com> Subject: Unable to drop duplicate stored procedure [13005]> Date: Thu, 7 Aug 2008 03:52:11 -0400> > Hi, would like to seek for your help. I accidentally created 2 stored > procedure with different parameter > > CREATE PROCEDURE store_reject_cancel( > > in_seq_no LIKE porders.seq_no, > > in_userid LIKE porders.userid > ) RETURNING CHAR(1), VARCHAR(100); > > and > > CREATE PROCEDURE store_reject_cancel( > > in_seq_no LIKE porders.seq_no, > > in_userid LIKE porders.userid, > > in_desc VARCHAR(100) > ) RETURNING CHAR(1), VARCHAR(100); > > When i try to drop the procedures, it prompt me the following error message > > drop command --> drop procedure store_reject_cancel > Error message --> 9700: Routine (store_reject_cancel) ambiguous - more than > one routine resolv > > So my question is how can i drop the stored procedure > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Get more from your digital life. Find out how. http://www.windowslive.com/default.html?ocid=TXT_TAGLM_WL_Home2_082008

On Thu, Aug 7, 2008 at 4:40 AM, Mark Tyrer <mark.tyrer@rtt.co.za> wrote: > I don't know what the gurus will say on this, but I would be very cautious > about data manipulation in the database system tables - sooner or later you > will make a mistake. > > Better to use the tools supplied to do the job! > > This message is best read by implicitly inserting OTC adjectives. I get uncomfortable when people tweak the system catalog by hand. I also get uncomfortable when xxxs a la xxx OTC are xxx liberally xxx interspersed into a xxx response. It's xxx hard to xxx see the xxx wood for the xxx expletives. Also, as a general comment, you can create stored procedures with a specific name - that must be unique - and then drop the procedure via that specific name. It won't help this time, but can save trouble next time. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Walter > Lowich > Sent: 07 August 2008 01:10 PM > To: ids@iiug.org > Subject: Re: Unable to drop duplicate stored procedure [13010] > > You may have to find the proc ids for both versions of your stored > procedures and then drop all rows from sysprocplan, sysprocbody, sysprocauth > and sysprocedures for that procid. Remember, sysprocedures must be done > last. This has worked for me in the past. > > Good luck. > > --- On Thu, 8/7/08, NG SOO CHEE <ng.soo.chee@gmail.com> wrote: > > From: NG SOO CHEE <ng.soo.chee@gmail.com> > Subject: Unable to drop duplicate stored procedure [13005] > To: ids@iiug.org > Date: Thursday, August 7, 2008, 3:52 AM > > Hi, would like to seek for your help. I accidentally created 2 stored > procedure with different parameter > > CREATE PROCEDURE store_reject_cancel(> > in_seq_no LIKE porders.seq_no, > > in_userid LIKE porders.userid > ) RETURNING CHAR(1), VARCHAR(100); > > and > > CREATE PROCEDURE store_reject_cancel(> > in_seq_no LIKE porders.seq_no, > > in_userid LIKE porders.userid, > > in_desc VARCHAR(100) > ) RETURNING CHAR(1), VARCHAR(100); > > When i try to drop the procedures, it prompt me the following error message > > drop command --> drop procedure store_reject_cancel Error message --> 9700: > Routine (store_reject_cancel) ambiguous - more than one routine resolv > > So my question is how can i drop the stored procedure > -- 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.

What is wrong with Drop procedure sp_name (int, int); Drop procedure sp_name (int, int, varchar); I believe there is a request in for Drop procedure all sp_name; And yes the all option can get you into a real mess :-) Cheers Paul -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jonathan Leffler Sent: Thursday, August 07, 2008 11:24 PM To: ids@iiug.org Subject: Re: Unable to drop duplicate stored procedure [13027] On Thu, Aug 7, 2008 at 4:40 AM, Mark Tyrer <mark.tyrer@rtt.co.za> wrote: > I don't know what the gurus will say on this, but I would be very cautious > about data manipulation in the database system tables - sooner or later you > will make a mistake. > > Better to use the tools supplied to do the job! > > This message is best read by implicitly inserting OTC adjectives. I get uncomfortable when people tweak the system catalog by hand. I also get uncomfortable when xxxs a la xxx OTC are xxx liberally xxx interspersed into a xxx response. It's xxx hard to xxx see the xxx wood for the xxx expletives. Also, as a general comment, you can create stored procedures with a specific name - that must be unique - and then drop the procedure via that specific name. It won't help this time, but can save trouble next time. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Walter > Lowich > Sent: 07 August 2008 01:10 PM > To: ids@iiug.org > Subject: Re: Unable to drop duplicate stored procedure [13010] > > You may have to find the proc ids for both versions of your stored > procedures and then drop all rows from sysprocplan, sysprocbody, sysprocauth > and sysprocedures for that procid. Remember, sysprocedures must be done > last. This has worked for me in the past. > > Good luck. > > --- On Thu, 8/7/08, NG SOO CHEE <ng.soo.chee@gmail.com> wrote: > > From: NG SOO CHEE <ng.soo.chee@gmail.com> > Subject: Unable to drop duplicate stored procedure [13005] > To: ids@iiug.org > Date: Thursday, August 7, 2008, 3:52 AM > > Hi, would like to seek for your help. I accidentally created 2 stored > procedure with different parameter > > CREATE PROCEDURE store_reject_cancel(> > in_seq_no LIKE porders.seq_no, > > in_userid LIKE porders.userid > ) RETURNING CHAR(1), VARCHAR(100); > > and > > CREATE PROCEDURE store_reject_cancel(> > in_seq_no LIKE porders.seq_no, > > in_userid LIKE porders.userid, > > in_desc VARCHAR(100) > ) RETURNING CHAR(1), VARCHAR(100); > > When i try to drop the procedures, it prompt me the following error message > > drop command --> drop procedure store_reject_cancel Error message --> 9700: > Routine (store_reject_cancel) ambiguous - more than one routine resolv > > So my question is how can i drop the stored procedure > -- 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. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Jonathan Leffler wrote: > On Thu, Aug 7, 2008 at 4:40 AM, Mark Tyrer <mark.tyrer@rtt.co.za> wrote: > >> I don't know what the gurus will say on this, but I would be very cautious >> about data manipulation in the database system tables - sooner or later you >> will make a mistake. >> >> Better to use the tools supplied to do the job! >> >> This message is best read by implicitly inserting OTC adjectives. >> > > I get uncomfortable when people tweak the system catalog by hand. > > I also get uncomfortable when xxxs a la xxx OTC are xxx liberally xxx > interspersed into a xxx response. It's xxx hard to xxx see the xxx > wood for the xxx expletives. > > I just can't fucking win. How did I get fucking dragged into this? :o( -- Cheers, Obnoxio the Clown http://obotheclown.blogspot.com

On Fri, Aug 8, 2008 at 3:06 AM, Obnoxio The Clown <obnoxio@serendipita.com> wrote: > Jonathan Leffler wrote: >> I also get uncomfortable when xxxs a la xxx OTC are xxx liberally xxx >> interspersed into a xxx response. It's xxx hard to xxx see the xxx >> wood for the xxx expletives. > > I just can't fucking win. How did I get fucking dragged into this? :o( Simple - X marks the spot. -- 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.