CODEWITHSUNDEEP
plsqloracle11gpls-00103
user3391904
user3391904

Reputation: 7

PLSQL procedure error

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;

Upvotes: 0

Views: 252

Answers (2)

Alex Poole
Alex Poole

Reputation: 191560

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).

Upvotes: 2

steve
steve

Reputation: 6020

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

Upvotes: 0

Related Questions