Unable to create Procedure after Dropping

Posted in 2010

Summary

A user dropped a stored procedure/function named to_number and then couldn't recreate it, getting "Another routine with same signature already exists in database." Suggestions were to run dbschema -f for the routine (which reported no such procedure) and to query sysprocedures for anything matching to_number. Fernando Nunes noted that IDS 11 ships a built-in to_number() function, so a user function of that name can't be created. No fix was recorded: the user simply rebuilt the (test) database.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Stored Procedures & SPL

RON TAY — Mon, 02 Aug 2010 10:06:42 — source: IIUG Forums & Mailing Lists

Hi All, I dropped a Procedure and was unable to recreate it. I keep getting the error "Another routine (routine_name) with same signature already exists in database." Please help to give me some advices. Thanks All...

↪ replying to RON TAY

Obnoxio The Clown — Mon, 02 Aug 2010 11:09:35 — source: IIUG Forums & Mailing Lists

RON TAY wrote: > Hi All, > > I dropped a Procedure and was unable to recreate it. I keep getting the error > "Another routine (routine_name) with same signature already exists in > database." > > Please help to give me some advices. Run a dbschema for that routine name and see what you find. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

↪ replying to RON TAY

Art Kagel — Mon, 02 Aug 2010 11:13:51 — source: IIUG Forums & Mailing Lists

Run "dbschema -d <database> -f <procname> and see what other versions of the procedure already exist. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) 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 Mon, Aug 2, 2010 at 6:06 AM, RON TAY <rontay79@yahoo.com.sg> wrote: > Hi All, > > I dropped a Procedure and was unable to recreate it. I keep getting the > error > "Another routine (routine_name) with same signature already exists in > database." > > Please help to give me some advices. > > Thanks All... > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cd48f0a5d7523048cd54b55

↪ replying to Art Kagel

RON TAY — Tue, 03 Aug 2010 00:58:59 — source: IIUG Forums & Mailing Lists

Hi Art & Obnoxio, I tried running dbschema on the procname and I gets "No procedure to_number". But when i tried running the script below, I still gets "Another routine (to_number) with same signature already exists in database" Do I have to restart IDS? Script Start---- CREATE function to_number(str varchar(255)) RETURNING INTEGER;return str; END function; Script End----

↪ replying to RON TAY

Fernando Nunes — Tue, 03 Aug 2010 09:02:59 — source: IIUG Forums & Mailing Lists

I don't have the first messages of this thread, so I don't know what happened and your server version. But if you're running IDS 11, then it already has a function called to_number().... You won't be able to create one with the same name... It's documented in the syntax guide. Regards. On Tue, Aug 3, 2010 at 1:59 AM, RON TAY <rontay79@yahoo.com.sg> wrote: > Hi Art & Obnoxio, > > I tried running dbschema on the procname and I gets "No procedure > to_number". > But when i tried running the script below, I still gets "Another routine > (to_number) with same signature already exists in database" > Do I have to restart IDS? > > Script Start---- > CREATE function to_number(str varchar(255)) RETURNING INTEGER;> return str; > END function; > Script End---- > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0015175cdfaa18146b048ce795e3

↪ replying to RON TAY

Art Kagel — Tue, 03 Aug 2010 17:10:35 — source: IIUG Forums & Mailing Lists

Post the results of: SELECT * FROM sysprocedures WHERE procname matches '*to_number*'; Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) 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 Mon, Aug 2, 2010 at 8:59 PM, RON TAY <rontay79@yahoo.com.sg> wrote: > Hi Art & Obnoxio, > > I tried running dbschema on the procname and I gets "No procedure > to_number". > But when i tried running the script below, I still gets "Another routine > (to_number) with same signature already exists in database" > Do I have to restart IDS? > > Script Start---- > CREATE function to_number(str varchar(255)) RETURNING INTEGER;> return str; > END function; > Script End---- > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e0cb4e887827008ff1048cee6516

↪ replying to Art Kagel

RON TAY — Thu, 12 Aug 2010 07:19:00 — source: IIUG Forums & Mailing Lists

Hi Art & Guys, In the end, I didn't really resolve the problem. My colleague and I decided to just recreate the whole DB since it was still a Test DB. Thanks guys for your replies and advices.