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

    function for VPD

    896548
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        Now, I understand. Thank you very much.
                        All best to you
                        Krzysztof
                        • 9. Re: function for VPD
                          stefan nebesnak
                          You can also use the CHR() function in a query:

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