drop procedure if exists in DB Oracle

Question

Can someone tell me how I can drop a PROCEDURE in Oracle, but just if it exists ?

DROP PROCEDURE IF EXISTS XYZ;

The above does not work.

No, In Oracle, you don't have this feature. – Incognito Feb 20 '14 at 14:52 — Incognito, Feb 20 '14 at 14:52

score 11 · Accepted Answer · edited Feb 21 '14 at 02:54

11

If your goal is to eliminate error messages in a script, then you can try

begin
   execute immediate 'drop procedure xyz';
exception when others then
   if sqlcode != -4043 then
      raise;
   end if;
end;
/

edited Feb 21 '14 at 02:54

Jeffrey Kemp

59,135
14
106
158

answered Feb 20 '14 at 14:54

René Nyffenegger

39,402
33
158
293

1

"Exception when others then" is almost always a bug waiting to happen. In this case, the idea is to drop procedure xyz, and mask the ORA-04043 error that would normally occur if the procedure you attempt to drop already doesn't exist. That's fine, but what if procedure XYZ *does* exist, but the user attempting the drop doesn't have permission to drop it? The drop would raise ORA-01031, but it would be ignored, due to the 'when others'. Better to declare the exception you want to ignore, specifically, than to use 'others'. – Mark J. Bobak Feb 20 '14 at 17:42

score 2 · Answer 2 · answered Feb 20 '14 at 16:27

2

You can also check dictionary view before:

SELECT * FROM USER_PROCEDURES WHERE PROCEDURE_NAME = 'XYZ'

answered Feb 20 '14 at 16:27

Wernfried Domscheit

54,457
9
76
110

Notice that `PROCEDURE_NAME` will only work for procedures in packages, you should use `OBJECT_NAME` instead for all kind of functions – Eugene K Aug 14 '20 at 19:19
@EugeneK inside a package you cannot drop a procedure anyway – Wernfried Domscheit Aug 15 '20 at 07:24
so are you saying that your answer will not help topic starter? – Eugene K Aug 15 '20 at 17:27

score 2 · Answer 3 · answered Jan 30 '18 at 11:44

My solution:

DECLARE
  V_NUM NUMBER;
BEGIN     
  SELECT COUNT(*)
  INTO   V_NUM
  FROM   USER_OBJECTS
  WHERE  OBJECT_NAME = 'XYZ'
  AND    OBJECT_TYPE = 'PROCEDURE'; 

  IF V_NUM > 0 THEN
    EXECUTE IMMEDIATE 'DROP PROCEDURE XYZ';
    DBMS_OUTPUT.PUT_LINE('Dropped');
  END IF;

END;
/

score 1 · Answer 4 · answered Feb 16 '18 at 10:21

1

A complete example:

declare
   c int;
begin
       select count(*) into c from user_procedures where object_type = 'FUNCTION' and object_name = 'ABC';
       if c = 1 then
          execute immediate 'DROP FUNCTION ABC';
       end if;
end;

answered Feb 16 '18 at 10:21

Sheen

3,333
5
26
46

If you have an invalid procedure (due to compiling errors), the invalid procedure is listet in `USER_OBJECTS` and `ALL_OBJECTS` but not listed in `USER_PROCEDURES` and `ALL_PROCEDURES`. See also: [Question 5721474](https://stackoverflow.com/questions/5721474/how-to-check-if-a-stored-procedure-exist) – Peter A Apr 24 '18 at 09:42

drop procedure if exists in DB Oracle

4 Answers4