SELECT FOR UPDATE Fails with ORA-02014 When Executed On A Nested Table Protected By RLS (Doc ID 452303.1)

Last updated on JANUARY 27, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.4
Information in this document applies to any platform.

Symptoms

A SELECT FOR UPDATE query will fail with ORA-02014 when executed on a nested table protected by a Row Level Security (RLS) policy:

 

drop user foo cascade;
grant connect,resource to foo identified by foo;
grant execute on dbms_rls to foo;


connect foo/foo


create or replace function pf1 (oowner in varchar2,ojname in varchar2) return varchar2 as
begin
return '(-29.0905=-29.0905))';
end;
/



create type nst_typ as table of varchar2(10)
/
create table foo_tab(n number,ct nst_typ) nested table ct store as ct_tab;


begin
    sys.dbms_rls.add_policy(
    object_schema=>'FOO',
    object_name=>'FOO_TAB',
    policy_name=>'TKXF_ADD_VPD_POLICY',
    function_schema=>'FOO',
    policy_function=>'pf1',
    statement_types=>'select,insert,delete,update',
    update_check=>true,
   policy_type=>dbms_rls.dynamic
   );
   end;
/


select n from foo_tab for update;
select n from foo_tab for update
              *
ERROR at line 1:
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms