Getting error when trying to run SQL statement in a oracle function

Question

create or replace function auditif
return number as
l_filter_count  NUMBER;
begin
 EXECUTE IMMEDIATE 'EXPLAIN PLAN FOR ' || SYS_CONTEXT('USERENV','CURRENT_SQL');
select count(*)
     into l_filter_count
     from table(dbms_xplan.display(format=>'PREDICATE'))
     where (plan_table_output like '% - filter(%' OR plan_table_output like '% - access(%')
     and plan_table_output not like '%SYS_AUDIT(%';
     dbms_output.put_line(l_filter_count);
if  (SUBSTR(UPPER(sys_context('userenv','CLIENT_PROGRAM_NAME')),1,3)='SQL' AND l_filter_count=0)
or 
(SUBSTR(UPPER(sys_context('userenv','CLIENT_PROGRAM_NAME')),1,4)='TOAD' AND l_filter_count=0)
then return 1;
else return 0; 
end if;
end;
/  

I have called this function inside a DBMS_FGA policy for enable audit conditionally

begin
  DBMS_FGA.ADD_POLICY(
    OBJECT_SCHEMA =>'AIM_DBA',
    object_name =>'EMP_DATA_I',
    policy_name =>'PROTECT_AIM_DBA_SCHEMA_TABLE',
    audit_condition=>'AIM_DBA.AUDITIF=1',
    statement_types =>'UPDATE,DELETE');
end;
/

But when running sql statement to check audit policy functionality getting following error:

delete EMP_DATA_I
*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at "AIM_DBA.AUDITIF", line 5

Answer 1

The result of the context call appears to be null, so you're attempting to run

explain plan for 

as a complete statement, with nothing after the for ; which will throw that error.

From the DBMS_FGA docs:

The audit_condition must be a boolean expression that can be evaluated using the values in the row being inserted, updated, or deleted. The expression can also use functions, such as the USER or SYS_CONTEXT functions.

The expression must not combine conditions using operators such as AND and OR. audit_condition can be NULL (or omitted), which is interpreted as TRUE, but it cannot contain the following elements:

• Subqueries or sequences
• The following attributes of the USERENV namespace when accessed using the SYS_CONTEXT function:
  • CURRENT_SQL
  • CURRENT_SQL_LENGTH
  • CURRENT_BIND
• Any use of the pseudo columns LEVEL, PRIOR, or ROWNUM.

And in the sys_context docs:

CURRENT_SQL returns the first 4K bytes of the current SQL that triggered the fine-grained auditing event. ... You can specify these attributes only inside the event handler for the fine-grained auditing feature.

So you can use that in handler_module function, but not in a function you call as part of the audit_condition.