Geek asks and answers

Drop if exists in an Oracle database

Can someone tell me how can I abandon the PROCEDURE in Oracle, but only if it exists?

DROP PROCEDURE IF EXISTS XYZ;

The above does not work.

+3
source share
4 answers

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

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

You can also check the dictionary dictionary before:

SELECT * FROM USER_PROCEDURES WHERE PROCEDURE_NAME = 'XYZ'
+2
source

Full 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;
+1
source

My decision:

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;
/
0
source

More articles:


All Articles