This discussion is archived
5 Replies Latest reply: Sep 10, 2010 2:05 AM by user651567 RSS

how to pass parameter in a view

user651567 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.
  • 5. Re: how to pass parameter in a view
    user651567 Newbie
    Currently Being Moderated
    yes

Legend

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