This discussion is archived
9 Replies Latest reply: Jan 26, 2013 7:03 AM by stefan nebesnak RSS

function for VPD

896548 Newbie
Currently Being Moderated
Please help me in below function to VPD
create or replace function vpd_fcn (vpd_schema varchar2, vpd_object varchar2)
return varchar2
is
v_user varchar2(50);
Predicate varchar2(2000);
begin
v_user :=   SYS_CONTEXT('USERENV', 'SESSION_USER');
if (v_user = 'KRZYSZTOF') then
Predicate := NULL;
else
Predicate := 'USERREG = ''v_user'';
end if;
return Predicate;
end;
/
Line >>Predicate := 'USERREG = ''v_user''; << how to declare this line with v_user varialbe to VPD.


Thank you.
Regards
Krzysztof
  • 1. Re: function for VPD
    stefan nebesnak Journeyer
    Currently Being Moderated
    Predicate := 'USERREG = ' || v_user;
    
    "or"
    
    Predicate := 'USERREG = ' || '''' ||v_user || '''';
    (depends on your needs)

    Edited by: stefan nebesnak on Jan 26, 2013 4:57 AM
  • 2. Re: function for VPD
    Hoek Guru
    Currently Being Moderated
    Perhaps you want to concat:
    your example:
    SQL> create or replace function vpd_fcn (vpd_schema varchar2, vpd_object varchar2)
      2  return varchar2
      3  is
      4  v_user varchar2(50);
      5  Predicate varchar2(2000);
      6  begin
      7  v_user :=   SYS_CONTEXT('USERENV', 'SESSION_USER');
      8  if (v_user = 'KRZYSZTOF') then
      9  Predicate := NULL;
     10  else
     11  Predicate := 'USERREG = ''v_user'';
     12  end if;
     13  return Predicate;
     14  end;
     15  /
    
    Warning: Function created with compilation errors.
    
    SQL> sho err
    Errors for FUNCTION VPD_FCN:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    11/14    PLS-00103: Encountered the symbol "USERREG = 'v_user';
             end if;
             return Predicate;
             end;" when expecting one of the following:
             ( - + case mod new not null <an identifier>
             <a double-quoted delimited-identifier> <a bind variable>
             continue avg count current exists max min prior sql stddev
             sum variance execute forall merge time timestamp interval
             date <a string literal with character set specification>
             <a number> <a single-quoted SQL string> pipe
             <an alternatively-quoted string literal with
    Adjusted:
    SQL> create or replace function vpd_fcn (vpd_schema varchar2, vpd_object varchar2)
      2  return varchar2
      3  is
      4  v_user varchar2(50);
      5  Predicate varchar2(2000);
      6  begin
      7  v_user :=   SYS_CONTEXT('USERENV', 'SESSION_USER');
      8  if (v_user = 'KRZYSZTOF') then
      9  Predicate := NULL;
     10  else
     11  Predicate := 'USERREG = '''||v_user||''' ';
     12  end if;
     13  return Predicate;
     14  end;
     15  /
    
    Function created.
    
    SQL> select vpd_fcn(null,null) from dual;
    
    VPD_FCN(NULL,NULL)
    --------------------------------------------------------------------------------
    USERREG = 'HOEK'
    
    1 row selected.
  • 3. Re: function for VPD
    896548 Newbie
    Currently Being Moderated
    Thank you very much. It's works. I won't never get it.
    But please explain me why there is necessary to used 3 apostrophes?
  • 4. Re: function for VPD
    Hoek Guru
    Currently Being Moderated
    You need 3 (actually 4, see queries below) apostrophes since you'll need socalled 'escape quotes (or characters)'.
    See:
    http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements003.htm#SQLRF00218
    SQL> select '' from dual;
    
    '
    -
    
    
    1 row selected.
    
    SQL> select ''' from dual;
    ERROR:
    ORA-01756: String tussen aanhalingstekens niet juist beëindigd.
    
    
    SQL> select '''' from dual;
    
    '
    -
    '
    
    1 row selected.
    So, in short: in sql -- '' is a '
  • 5. Re: function for VPD
    Etbin Guru
    Currently Being Moderated
    Taking a look at [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements003.htm#SQLRF00218]Text Literals could make it a bit easier.

    Regards

    Etbin
  • 6. Re: function for VPD
    896548 Newbie
    Currently Being Moderated
    I see
    first 2 apostrophes is for start and end string
    second 2 apostrophes give sign apostrophe (')
    third 2 apostrophes again start and stop string (for placed some variable).

    Am I right?
  • 7. Re: function for VPD
    Etbin Guru
    Currently Being Moderated
    Am I right?
    I'm afraid not.
    First apostrophe = start of literal.
    Middle two apostrophes count as a single apostrophe when between first and last apostrophe.
    Last apostrophe = end of literal.

    I suggested q notation use, so you don't have to think about the rule above

    <tt>query := 'select to_char(sysdate,''dd.mm.yyyy hh24:mi:ss'') from dual';</tt>
    could be written as
    <tt>query := q'~select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') from dual~';</tt>

    Regards

    Etbin

    Edited by: Etbin on 26.1.2013 15:53
    Didn't use various brackets as delimiters (was afraid of forum's (mis)interpetation)
  • 8. Re: function for VPD
    896548 Newbie
    Currently Being Moderated
    Now, I understand. Thank you very much.
    All best to you
    Krzysztof
  • 9. Re: function for VPD
    stefan nebesnak Journeyer
    Currently Being Moderated
    You can also use the CHR() function in a query:

    See ASCII table.
    Predicate := 'USERREG = ' || CHR(39) || v_user || CHR(39);

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points