5 Replies Latest reply: Sep 10, 2010 4:05 AM by user651567 RSS

    how to pass parameter in a view

    user651567
      Dear all,
      i have doudt that.
      Can we a pass value in a view??

      i have heard that with the help of contet in view e can solve this problem.

      but i don't remeber that one .
      ple help me in this.
        • 1. Re: how to pass parameter in a view
          Herald ten Dam
          Hi,

          see: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1448404423206

          Herald ten Dam
          http://htendam.wordpress.com
          • 2. Re: how to pass parameter in a view
            706888
            Hi,

            1) in 99% of the cases, you don't need to do so because you'll need to query the view so why not provide this parameter when you query the view?
            2) if you have no control over the query used to read the view and if the content of the view is dependant on the user, then i suggest you to use VPDs...
            • 3. Re: how to pass parameter in a view
              Saubhik
              Are you looking for something like this ?
              SQL> ed
              Wrote file afiedt.buf
              
                1  CREATE OR REPLACE VIEW MY_EMP_VIEW AS
                2  SELECT e.ename,e.deptno,d.dname
                3  FROM emp e,dept d
                4  WHERE e.deptno=d.deptno
                5* AND USERENV('CLIENT_INFO')=e.deptno
              SQL> /
              
              View created.
              
              SQL> select *from MY_EMP_VIEW;
              
              no rows selected
              
              SQL> EXECUTE dbms_application_info.set_client_info(30);
              
              PL/SQL procedure successfully completed.
              
              SQL> select *from MY_EMP_VIEW;
              
              ENAME                                   DEPTNO DNAME
              ----------------------------------- ---------- ----------
              ALLEN                                       30 SALES
              WARD                                        30 SALES
              MARTIN                                      30 SALES
              BLAKE                                       30 SALES
              TURNER                                      30 SALES
              JAMES                                       30 SALES
              999999                                      30 SALES
              
              7 rows selected.
              Here, we can set dynamically the department number we want to view from the view created. These technique is used (in a more complex way) in multi-org system in Oracle Apps 11i with database 9i/10G.
              But now VPD and FGA is more powerful with latest oracle database.