Student E.C., this one’s for you. Here’s E.C.’s problem code:
begin dbms_rls.add_policy(‘oe’, ‘customers’, ‘accesscontrol_customers’, ’system’, ‘f_policy_customers2′, sec_relevant_cols=>’CREDIT_LIMIT’, sec_relevant_cols_opt => dbms_rls.ALL_ROWS); end; /The error was ‘ORA-28101: policy already exists.’ So how do we attack this? The first thing I do when some PL/SQL simply doesn’t work, I check the PL/SQL packages reference:
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/toc.htm
Having got the definition of DBMS_RLS, put in all parameter names.
begin dbms_rls.add_policy( object_schema => ‘oe’, object_name => ‘customers’, policy_name => ‘accesscontrol_customers’, function_schema => ‘system’, policy_function => ‘f_policy_customers2′, sec_relevant_cols => ‘CREDIT_LIMIT’, sec_relevant_cols_opt => dbms_rls.ALL_ROWS); end; /Once you name all the parameters, the parameter of interest here should be clear. But how do we tell whether this is really the culprit?
Well, every last thing in Oracle is in some dictionary table or view, including policies. So we query USER_POLICIES or ALL_POLICIES for policy name ‘accesscontrol_customers’. And that, E.C., will I think give you your answer! What do you think?