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?
Add a comment
|
3 Answers
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.
Comments
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
Comments
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 :
DROP FUNCTION function_1;
DROP FUNCTION function_2;
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;