Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using informix...

I dont know how i did it, but there are two procedures with the same name in my database. When i try to remove them with a

DROP PROCEDURE myProc;

then i get a error message

ERROR: Routine (add_adr_trigger_row) ambiguous - more than one
routine resolves to given signature.
Error Code: -9700

How can i drop the procedures?

share|improve this question

2 Answers 2

up vote 10 down vote accepted

This happens when you have 2 or more procedures, with the same name, but with different numbers of input parameters.

For example, you have created 2 procedures:

CREATE PROCEDURE myProc(param1)
...
CREATE PROCEDURE myProc(param1, param2)
...

To delete the second one, you have 2 options:

The easy one:

DROP PROCEDURE myProc(param1, param2);

The hard one:

dbaccess DB -
select procname, procid, numargs from sysprocedures where procname like 'myProc';
procname  myProc
procid    1
numargs   1

procname  myProc
procid    2
**numargs   2**

UPDATE sysprocedures SET procname='myProcOLD' WHERE procid=2;
DROP PROCEDURE myProcOLD;

Even if the first method is dead simple, the first time I got called in the middle of the night for this same problem, I've chosen the second. My bad ...

share|improve this answer
    
Also, you can use "finderr 9700" in your ssh console to see more info on this kind of error. The info is often very usefull: ... This problem happens when an argument (or its source type or parent type) has implicit casts to the parameters of two or more routines. For example, assume two routines exist called routine_name(paramtype1) and routine_name(paramtype2), and routine_name is invoked with routine_name(argtype). Also, implicit casts exist from argtype to paramtype1 and argtype to paramtype2. In this case, this error is raised. –  MTIhai Dec 21 '11 at 14:15
    
Thanks a lot @MTIhai It works perfectly! –  PeterP Dec 21 '11 at 14:49

If you are aware that this might be a problem in the future, you can create your procedure with a SPECIFIC name, which must be unique across all procedures in the database.

If you aren't aware that it will be a problem when you create the procedure, then you can't officially go back and add a specific name, and you do have a problem.

The UPDATE of sysprocedures in the selected answer should only work if the user is connected as informix (or, in the case of a private server, the server owner).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.