Search code examples
functionplsqlblockprocedureplsqldeveloper

How do i drop a function and a procedure in the same anonymous pl/sql block?

I have this procedure:

 create or replace procedure show_emp
    is
    cursor c is select name,id_funct,date_emp from emp;
...

And this function:

create or replace FUNCTION raise(cod_ang INTEGER, sum REAL) 
RETURN VARCHAR
  IS
...

Everything is correctly implemented.

How do i delete this procedure and this function in an anonymous pl/sql block? When i try:

begin
drop procedure show_emp;
drop function raise;
end;

I recive this error:PLS-00103: Encountered the symbol "DROP" when expecting one of the following:

Solution

  • drop is a DDL Statement and you cant use it in pl/SQL directly.

    just do

    drop procedure show_emp;
drop function raise;

    or use dynamic SQL to execute ddl statement

    begin
  execute immediate 'drop procedure show_emp';
  execute immediate 'drop function raise';
end;