CREATE OR REPLACE v/s Dropping a function and re-creating it

database, oracle, plsql, sql

Solution

When an object is dropped everything associated with it is dropped too, including privileges. This is not true of CREATE OR REPLACE.

SQL> create procedure p1 is
  2  begin
  3      null;
  4  end;
  5  /

Procedure created.

SQL> grant execute on p1 to xyz;

Grant succeeded.

SQL> select * from user_tab_privs_made
  2  /

GRANTEE                        TABLE_NAME                     GRANTOR                        PRIVILEGE                     GRA HIE
------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --- ---
XYZ                            P1                             APC                            EXECUTE                       NO  NO

SQL> create or replace procedure p1 is
  2    n pls_integer;
  3  begin
  4   n := 1;
  5  end;
  6  /

Procedure created.

SQL> select * from user_tab_privs_made
  2  /

GRANTEE                        TABLE_NAME                     GRANTOR                        PRIVILEGE                     GRA HIE
------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --- ---
XYZ                            P1                             APC                            EXECUTE                       NO  NO

SQL> drop procedure p1;

Procedure dropped.

SQL> create or replace procedure p1 (p in out pls_integer) is
  2  begin
  3    p := p+1;
  4  end;
  5  /

Procedure created.

SQL> select * from user_tab_privs_made
  2  /

no rows selected

SQL>

Problem

What is the difference between the two? In both the cases? what happens to the privileges granted on this function? Are automatically revoked in both the cases and have to be provided again while re-creating? Kindly explain.

Original source