PLSQL procedure error

0
votes

I get the below error while executing code

create or replace
function contact_restriction_function(obj_schema varchar2, obj_name varchar2)
return varchar2 is
    v_contact_info_visible hr_user_access.contact_info_visible%type;
    begin
    -- Here you can put any business logic for filtering
    select nvl(max(contact_info_visible),'N')
    into v_contact_info_visible
    from hr_user_access
    where user_name = user;

    -- SQL filter / policy predicate
    return ''''||v_contact_info_visible||''' = ''Y'' ';
end;
/

after show erros command i got this

 show errors    
Errors for FUNCTION CONTACT_RESTRICTION:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1      PLS-00103: Encountered the symbol "?" when expecting one of the
         following:
         begin function pragma procedure subtype type 
          current cursor delete
         exists prior external language

This is the remaining code:

begin
  dbms_rls.add_policy(object_schema => 'HR' ,
                      object_name => 'EMPLOYEES' ,
                      policy_name => 'Contact_Restriction_Policy' ,
                      policy_function => 'contact_restriction_function' ,
                      sec_relevant_cols=>'EMAIL,PHONE_NUMBER'Contact Info ,
                      sec_relevant_cols_opt=>dbms_rls.all_rows);
end;

below is the actual code which i am executing before show errors:

create or replace function contact_restriction(obj_schema varchar2, obj_name varchar2) 
  return varchar2
is
  v_contact_info_visible IN user_access.contact_info_visible%type;
begin
  select nvl(max(contact_info_visible),'N')
    into v_contact_info_visible
    from user_access where username = user;

  return 'v_contact-info_visible ='|| 'Y';
end;
2
plsqloracle11gpls-00103
As there is no "?" character in the code you posted I find it tough to diagnose this problem. Perhaps there was some other code that wasn't included? – Bob Jarvis - Reinstate Monica
this is the remaining code – user3391904
You've shown code for contact_restriction_function, but the error is for CONTACT_RESTRICTION. Please show the code you actually executed immediately before calling show errors. – Alex Poole
In the call to DBMS_RLS.ADD_POLICY mentioned in your comment (above) you're passing policy_function => 'contact_restriction_function' - but because unquoted identifiers in Oracle default to UPPER CASE this should be policy_function => 'CONTACT_RESTRICTION_FUNCTION'. This may not be the source of this problem but may be helpful in the future. Share and enjoy. – Bob Jarvis - Reinstate Monica
@user3391904 - please put code in the question, not in comments. That still doesn't give the error message with "?"; but it will complain about the IN in the declaration of v_contact_info_visible. Not sure why you're stripped the quote handling from the sample you copied; you've also changed an underscore to a hyphen, which will cause another error. – Alex Poole

2 Answers

2
votes

Your original question shows an error message referring to "?", but the code yout posted a as comment would raise a similar error for `"IN"' instead:

2/24   PLS-00103: Encountered the symbol "IN" when expecting one of the following:

That is because you've used IN for a local variable; but IN, OUT and IN OUT are only applicable to stored procedure parameters. You could have declared the function with an explicit IN for example, though it is the default anyway:

create or replace function contact_restriction(obj_schema IN varchar2, ...

So that needs to be removed from the v_contact_info_visible declaration. You've linked to an example you're working from, but you've removed a lot of important quotes from that, which will still cause it to fail when executed as a part of a VPD; because v_contact_info_visible will be out of scope to the caller. And you have a typo, with a hyphen instead of an underscore.

You need something like:

create or replace function contact_restriction(obj_schema varchar2,
  obj_name varchar2)
return varchar2 is
  v_contact_info_visible user_access.contact_info_visible%type;
begin
  select nvl(max(contact_info_visible),'N')
  into v_contact_info_visible
  from user_access
  where username = user;
  return ''''||v_contact_info_visible ||''' =''Y''';
end;
/

When called, that will return a string which is either 'N'='Y' or 'Y'='Y'. VPD will include that as a filter in the original query, which will either prevent any rows being returned (in the first case) or have no effect and allow all rows that match any other existing conditions to be returned (in the second case).

0
votes

The syntax of the function header is incorrect. It should be:

create or replace function contact_restriction(obj_schema IN varchar2, obj_name IN varchar2) 
  return varchar2
is