Oracle Apex ORA-14551: cannot perform a DML operation inside a query tips

Question

I use APEX 4.2.

I've an interactive report with this query:

    select col1, col2, col3, col4 
    from MyTable
    where function_check_user('ADMIN');

The function function_check_user() returns 1 if the user, who has the role 'ADMIN', can show the result of the query. I'm using the APEX procedure apex_util.public_check_authorization()

create or replace FUNCTION function_check_user
(
  xrole  VARCHAR2
)  
return integer IS
xcheck  BOOLEAN;

begin
    xcheck:= apex_util.public_check_authorization(xrole);
  if xcheck then
     return 1;
  end if;
     return 0;
end;

Problem: I set up the inetractive report with the query and try to run it on my Application. I had this error:

Oracle Apex ORA-14551: cannot perform a DML operation inside a query tips.

It works when I use the query without the function in the where clause:

select col1, col2, col3, col4 
from MyTable;

Is there an issue with the use of the procedure apex_util.public_check_authorization() ?

Thanks

Yes, looks like it. That error occurs when you attempt to use a function in a SQL statement that contains a DML statement (i.e. INSERT/UPDATE/DELETE/MERGE). You could maybe add a pragma autonomous_transaction to your function, or maybe Kishore's answer here may help? — Boneist
Not in your function, but it looks like there is inside apex_util.public_check_authorization. I bet if you took that function call out of your function, it'd work. — Boneist
Yes it works when I call it independently ! As you said I think there is a DML stat in the apex_util.public_check_authorization. thank you — O. Sam

Rene Rene · Accepted Answer · 2016-11-24T11:53:57

You can work around it by declaring pragma autonomous_transaction in the function.

create or replace FUNCTION function_check_user
(
  xrole  VARCHAR2
)  
return integer IS
pragma autonomous_transaction;
xcheck  BOOLEAN;

begin
    xcheck:= apex_util.public_check_authorization(xrole);
    rollback;
  if xcheck then
     return 1;
  end if;
     return 0;
end;

Oracle Apex ORA-14551: cannot perform a DML operation inside a query tips

1 Answers