hello gurus,
lately i wanted to create ddl trigger to prevent user from dropping its own table and it works fine;
the thing is, it also prevents user to recompile some of its functions when the change in function is made.
i tested every function in a package and it appears to me that ddl trigger which should work only on drop object works when i try to recompile function which returnes pipelined value(table), other functions which return row or procedures recompile without ddl trigger.
so can someone explain, why does oracle think that recompiling a pipelined function is the same as drop function?
and how do i fix this? what is the workaround?
here is the trigger ddl:
CREATE OR REPLACE TRIGGER db_trigger
BEFORE
DROP
ON DATABASE
begin
if ora_dict_obj_owner = 'BILL' and SYS_CONTEXT('USERENV','HOST')!='primdb' THEN
rati.prc_not_drop; --trigger executes this procedure for monitoring who tried to drop table and inserts it in log
RAISE_APPLICATION_ERROR (
num=> -20000,
msg=> 'you can't drop objects in BILL schema');
end if;
end;
CREATE OR REPLACE
PROCEDURE prc_not_drop
IS
pragma autonomous_transaction;
BEGIN
insert into rati.log values (sysdate, user, SYS_CONTEXT('USERENV','HOST'));
commit;
END;
and here is the package i can't recompile cause trigger thinks i drop it.
CREATE OR REPLACE
PACKAGE mypackage as
type emp_type is record (
no number,
empname varchar2(30),
job varchar2(30)
);
type emp_table is table of emp_type;
function emp_func return emp_table pipelined;
end mypackage;
CREATE OR REPLACE
PACKAGE BODY mypackage as
function emp_func
return emp_table pipelined as
emp_type_one emp_type;
begin
for i in (select empno, ename, job from scott.emp)
loop
emp_type_one.no:=i.empno;
emp_type_one.empname:=i.ename;
emp_type_one.job:=i.job;
pipe row (emp_type_one);
--pipe row (emp_type(i.empno, i.ename, i.job));
end loop;
return;
end;
end mypackage;
best regards.