Ora 04088 error during execution of trigger

While I was testing something on a 12.1 test database got this below error whenever I’m trying to execute specific admin commands:

SQL> drop user xx;
drop user xx
*
ERROR at line 1:
ORA-04088: error during execution of trigger ‘SYS.XDB_PI_TRIG’
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 3, column 13:
PLS-00302: component ‘IS_VPD_ENABLED’ must be declared
ORA-06550: line 3, column 5:
PL/SQL: Statement ignored

The above was just a sample but the error with showing up with lots of admin commands!

I checked the trigger SYS.XDB_PI_TRIG which causing this error and it was already valid, so I decided to DISABLE it, and then admin commands ran as usual:

Above failing admin commands have run smoothly:

Hello, we used exceltable package a few week ago and everything was fine. During this period, our oracle admins applied some patch or etc. and now we get an oracle error ORA-04088 which is connected with trigger checking grants to PUBLIC. I don’t know why something should grant privileges to PUBLIC, but it seems that error emerges on this row — «open l_rc for l_query using p_file, p_method, p_password;» in first getCursor function in EXCELTABLE package. We use ORACLE DB 12.2. Do you have any idea where is granting privileges used and why emerges this error? Thank you in advance.

1. This is part of our SQL code:
   declare
   piv_excel_name ext_ds_upt_kalendar_pro_kl.nazev_souboru%TYPE := ‘Kalendar.xlsx’;
   piv_nazev_listu ext_ds_upt_kalendar_pro_kl.nazev_listu%TYPE := ‘Plan’;
   cv_db_adresar CONSTANT VARCHAR2(16) := ‘DB_DIR’;
   lvr_data SYS_REFCURSOR;

begin
lvr_data :=
ExcelTable.getCursor(
p_file => ExcelTable.getFile(cv_db_adresar, piv_excel_name)
, p_sheet => piv_nazev_listu
, p_cols => ‘»A2″ VARCHAR2(100) column »A»’
, p_range => ‘A2:A2’
);
end;

1. This is whole error message:
   Error report —
   ORA-04088: error during execution of trigger ‘APPLDBA_P.BEFORE_GRANT_PUBLIC’
   ORA-00604: error occurred at recursive SQL level 3
   ORA-20997: Public grants on data schema objects not allowed
   ORA-06512: on line 23
   ORA-06512: on «EXT_STAGE.EXCELTABLE», line 4087
   ORA-06512: on line 11

1. 00000 — «error during execution of trigger ‘%s.%s'»
   *Cause: A runtime error occurred during execution of a trigger.
   *Action: Check the triggers which were involved in the operation.

1. This is the trigger mentioned above:
   create or replace TRIGGER appldba_p.before_grant_public BEFORE GRANT ON DATABASE
   declare
   vLst ora_name_list_t;
   vCnt int;

function is_authorized(p_owner varchar2,p_grantor varchar2) return varchar2 is
vRet varchar2(1);
begin
select decode(max(profile),’DAT_USER_PROFILE’,’F’,’T’) into vRet from dba_users where username=p_owner;
if vRet=’F’ then
select decode(count(*),0,’F’,’T’) into vRet from dba_role_privs where granted_role=’DBA’ and grantee=p_grantor;
end if;
return vRet;
end;

begin
if ora_dict_obj_name is null then
return;
end if;

vCnt:=ora_grantee(vLst);
for i in 1..nvl(vCnt,0) loop
if vLst(i)=’PUBLIC’ and is_authorized(ora_dict_obj_owner,ora_login_user)=’F’ then
raise_application_error(-20997,’Public grants on data schema objects not allowed’);
end if;
end loop;
end;

Problem Description
-------------------------------
You are creating a trigger that includes an exception handling block.  You wish to raise a user defined error when a certain condition is met within the trigger body using keyword RAISE.  
Inside your error handling block you also include a call to RAISE_APPLICATION_ERROR.


Consider this code example --
 create table tmp (col1 char(40));
create table violations (col1 varchar2(30));
 
CREATE OR REPLACE TRIGGER DEMO_RULE_001
BEFORE INSERT OR UPDATE ON TMP
FOR EACH ROW
DECLARE
  RULE_001 EXCEPTION;
BEGIN
  IF :NEW.col1 = 'mike' THEN
  dbms_output.put_line(:new.col1);
  INSERT INTO VIOLATIONS values ('violation logged');
  -- Raise rule
  RAISE RULE_001;
  END IF;
EXCEPTION
WHEN RULE_001 THEN  
  RAISE_APPLICATION_ERROR (-20001,'Guideline Violation, Rule-001.');
END; 
 
When this trigger is executed, you receive the ora-4088 and ora-6512 errors.
ORA-04088: error during execution of trigger 'SCOTT.DEMO_RULE_001'
 
 Solution Description
-------------------------------
 You cannot use both RAISE, within the execution block of a trigger, and RAISE_APPLICATION_ERROR, within the exception block.  
 Explanation
------------------------
 RAISE forces execution to move into the exception block.RAISE_APPLICATION_ERROR, within the exception block, terminates the program.If the trigger body does not complete, the triggering SQL statement and any 
SQL statements within the trigger body are rolled back. Thus, execution completes unsuccessfully with a runtime error and it appears as if none of the code within the trigger body gets executed.
 
Consider this corrected code --
CREATE OR REPLACE TRIGGER DEMO_RULE_001
BEFORE INSERT OR UPDATE ON TMP
FOR EACH ROW
DECLARE
  RULE_001 EXCEPTION;
BEGIN
  IF :NEW.col1 = 'mike' THEN
  dbms_output.put_line(:new.col1);
  INSERT INTO VIOLATIONS values ('violation logged');
  -- Raise rule
  RAISE RULE_001;
  END IF;
EXCEPTION
WHEN RULE_001 THEN  
  --raise_application_error(-20001, 'Guideline Violation, Rule-001.');
  dbms_output.put_line('Guideline Violation, Rule-001.');
END;
 
Oracle Support Doc ID 103293.1