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

From: xiaoyan <xiaoyezi.xyz_at_163.com>
Date: Thu, 31 Aug 2006 17:34:53 +0800
Message-ID: <002901c6cce0$b7afa500$d88870ca@buaad538c81ca1>

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.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Aug 31 2006 - 04:34:53 CDT