CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsqloracle-sqldeveloper
serene
serene

Reputation: 1656

how to drop multiple functions in Oracle

I want to drop multiple functions from my database at a time in Oracle's SQL developer. Is anyone has an idea how to create a script for that?

Upvotes: 1

Views: 1426

Answers (3)

user11997779
user11997779

Reputation:

There are a couple of options you can choose.

1. Generating SQL query to drop the functions

2. Using PL/SQL

#########################################################################

1. Generating SQL query to drop the functions

SELECT 'DROP FUNCTION ' || OBJECT_NAME || ';' FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION') AND OWNER = 'schemaName';

Above query generate below queries,

ex :

  1. DROP FUNCTION function_1;

  2. DROP FUNCTION function_2;

  3. DROP FUNCTION function_3;

After generating the all queries, you can execute them one by one or all in one as a script in SQL Developer.

#########################################################################

2. Using PL/SQL

DECLARE
    schemaName VARCHAR2(10) := 'your_schemaName';
    l_query VARCHAR2(500);
BEGIN

    -- drop all functions
    FOR objName IN (SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION') AND OWNER = schemaName)
    LOOP
        l_query := 'DROP FUNCTION ' || objName.OBJECT_NAME;
        --DBMS_OUTPUT.PUT_LINE(l_query);
        EXECUTE IMMEDIATE l_query;               
    END LOOP;
    
    -------------------------- example to drop all tables, types, procedures ------------------------------------
    -- drop all tables
    FOR objName IN (SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('TABLE') AND OWNER = schemaName)
    LOOP
        l_query := 'DROP TABLE ' || objName.OBJECT_NAME;
        --DBMS_OUTPUT.PUT_LINE(l_query);
        EXECUTE IMMEDIATE l_query;               
    END LOOP;
    
    -- drop all types
    FOR objName IN (SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('TYPE') AND OWNER = schemaName ORDER BY OBJECT_ID DESC)
    LOOP
        l_query := 'DROP TYPE ' || objName.OBJECT_NAME;
        --DBMS_OUTPUT.PUT_LINE(l_query);
        EXECUTE IMMEDIATE l_query;               
    END LOOP;
          
    -- drop all procedures
    FOR objName IN (SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('PROCEDURE') AND OWNER = schemaName)
    LOOP
        l_query := 'DROP PROCEDURE ' || objName.OBJECT_NAME;
        --DBMS_OUTPUT.PUT_LINE(l_query);
        EXECUTE IMMEDIATE l_query;               
    END LOOP;
    --------------------------------------------------------------------------------------------------------------
    
    COMMIT;
END;

Upvotes: 0

Marmite Bomber
Marmite Bomber

Reputation: 21170

Well, the static option to drop the functions (sometimes considered more secure as you can double-check what you are removing) consist of two steps

1 - run the following query

select 'drop function '||object_name||';'  as sql_txt
from user_objects where object_type = 'FUNCTION' 
and object_name like 'F%' /* filter the selection here */
order by 1;

SQL_TXT                                                                                                                                        
-----------------
drop function FA;
drop function FB;
drop function FC;

2 - copy the result drop statements in SQL Developer window and execute them

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143163

Well, you have to know which ones you want to drop. I named them all in the IN clause in the script that follows.

Test functions first:

SQL> create or replace function f1 return number is begin return 1; end;
  2  /

Function created.

SQL> create or replace function f2 return number is begin return 2; end;
  2  /

Function created.

Do they work?

SQL> select f1, f2 from dual;

        F1         F2
---------- ----------
         1          2

Yes, they do.

Let's drop them in a script:

SQL> begin
  2    for cur_r in (select object_name
  3                  from user_objects
  4                  where object_name in ('F1', 'F2')
  5                    and object_type = 'FUNCTION'
  6                 )
  7    loop
  8      execute immediate 'drop function ' || cur_r.object_name;
  9    end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

Do they work now?

SQL> select f1, f2 from dual;
select f1, f2 from dual
           *
ERROR at line 1:
ORA-00904: "F2": invalid identifier


SQL>

No, as they are dropped.

Upvotes: 4

Related Questions