CODEWITHSUNDEEP
sqloracle-databaseoracle11gpredicatepolicy
kyros
kyros

Reputation: 13

Virtual Private Database policy predicate has error

I'm trying to implement a vpd. So far I have created a function:

> CREATE OR REPLACE FUNCTION sales_select(
    schema_var IN VARCHAR2,
    table_var IN VARCHAR2
  )
  RETURN VARCHAR2
  IS
   return_val VARCHAR2(400);
  BEGIN
   return_val := 'time_id >= "01-JAN-01"';
  RETURN return_val;
  END sales_select;
  /

and the policy I made is the following:

L> BEGIN
2   DBMS_RLS.ADD_POLICY (
3    object_schema    => 'sh',
4    object_name      => 'costs1',
5    policy_name      => 'costs_policy',
6    function_schema  => 'policy_admin',
7    policy_function  => 'sales_select',
8    statement_types  => 'select'
9  );
0  END;
1  /

when I run the follow query:

select * from sh.costs1;

I get the following error:

    ERROR at line 1:
    ORA-28113: policy predicate has error

I'm thinking it has something to do with the quotes in the function but when I try changing them I get compile errors.

Upvotes: 1

Views: 624

Answers (1)

Marco Baldelli
Marco Baldelli

Reputation: 3728

Is time_id a date column? If so, try changing line 9 to:

return_val := 'time_id >= date ''2001-01-01''';

Note the double quotes.

Upvotes: 1

Related Questions