help:VPD and the error:ORA-01732: data manipulation operation not legal on this view

From: "xiaoyan" <xiaoyezi.xyz@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 31 Aug 2006 17:34:53 +0800

Hi,
  During my stduy of VPD,I got the following error:     ORA-01732: data 
manipulation operation not legal on this view 

  when I execute a delete statment on a TABLW not on a VIEW!

  There are two tables:SCORES and COURSES like the followings:

SQL>conn wxy/wxy;

SQL> select * from scores;

     SCORE   DESCRIPTION          
     ------   --------
        10    a                              
        20    b                             
        30    c                             
        50    d                             
        60    e    
                                                             
SQL> select * from courses;

 COURSE_ID COURSE_INFO                         COURSE_SCORE COURSE_NAME
---------- ------------------------------------------------------------ 
         4 efef                                    10            d
         3 tr                                      30            test
         6 t4t                                     50            test
         5 g4fe                                    10            b
         1 fe3fe                                   20            a
         2 rwfe3fe                                 50            f

Then I create two VPD functions:

SQL>conn test_system/test_system;

CREATE OR REPLACE  FUNCTION select_limits (schema_name_in  
VARCHAR2,table_name_in  VARCHAR2) RETURN VARCHAR2
IS
  predicate VARCHAR2(4000);

BEGIN         
    predicate:='score>30';
    RETURN predicate;          
END;

CREATE OR REPLACE  FUNCTION delete_limits (schema_name_in  
VARCHAR2,table_name_in  VARCHAR2) RETURN VARCHAR2
IS
   predicate VARCHAR2(4000);

BEGIN         
   predicate:='course_name=''test''';
   RETURN predicate;          
END;

And then I apply the policy to those two tables:

SQL> 
DBMS_RLS.ADD_POLICY('wxy','scores','select_policy','test_system','select_limits','select',TRUE,TRUE);
SQL> 
DBMS_RLS.ADD_POLICY('wxy','courses','delete_policy','test_system','delete_limits','delete',TRUE,TRUE);

Now I make some tests:

SQL>conn wxy/wxy;
SQL>select * from scores;
     
     SCORE   DESCRIPTION          
     ------   --------                               
        50    d                             
        60    e    
SQL>delete courses;
SQL>select * from courses;

 COURSE_ID COURSE_INFO                         COURSE_SCORE COURSE_NAME
---------- ------------------------------------------------------------ 
         4 efef                                    10            d         
         5 g4fe                                    10            b
         1 fe3fe                                   20            a
         2 rwfe3fe                                 50            f

SQL>rollback;

SQL>delete courses where course_score=(select score from scores where 
description=d);
    ORA-01732: data manipulation operation not legal on this view 

  Then I got the error!Who can tell me where is the problem and how to solve it?
  Thank you in advance!

  Best Regards.

help:VPD and the error:ORA-01732: data manipulation operation not legal on this view

Other related posts: