CODEWITHSUNDEEP
oracle-databasefunctionplsqlprocedure
Suwaid
Suwaid

Reputation: 1

Restrict Oracle from creating Compilation error Procedures

I want to know if there is a way to make Oracle not create a Procedure/Function if it has an error of any kind.

Upvotes: 0

Views: 103

Answers (2)

Michael Broughton
Michael Broughton

Reputation: 4055

In a SQL Script after your CREATE you could check ALL_ERRORS (or dba_errors) for an error on that object and, if errors exist, EXECUTE IMMEDIATE to drop it.

e.g.)

set serveroutput on

create or replace procedure meowner.testproc
as
begin
  select;
end;
/

DECLARE
  x number;
begin  
   select count(*) into x from all_errors
   where  owner = 'MEOWNER' and NAME='TESTPROC' ;
   if x != 0 then
      EXECUTE IMMEDIATE 'DROP PROCEDURE meowner.testproc';
      dbms_output.put_line('Dropped due to compile error');
   end if;
end;
/

PROCEDURE TESTPROC compiled
Errors: check compiler log
Dropped due to compile error
anonymous block completed

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142713

There's none, as far as I can tell. A stored procedure will be created in invalid state.

Though, you could try to create a wrapper procedure which would

  • create a procedure
  • check its status
  • drop it if it is invalid

Upvotes: 0

Related Questions