1 2 Previous Next 17 Replies Latest reply: Oct 24, 2007 8:07 AM by 546595 RSS

    Can you pass parameters to a view?

    594752
      Or do you have to use a stored procedure? In either case, what is the syntax? I have 2 variables defined in the original select statement. I would like to execute the view/stored procedure. They are actually 2 dates.
        • 1. Re: Can you pass parameters to a view?
          Paul M.
          Of course you can do that with a procedure, but you can do it with a view as well
          SQL> select * from tab where tname = 'V_EMP';

          TNAME                          TABTYPE       CLUSTERID
          ------------------------------ ------- ---------------
          V_EMP                          VIEW

          SQL> select * from v_emp order by hiredate;

                    EMPNO ENAME      HIREDATE
          --------------- ---------- --------------------
                     7698 BLAKE      01/05/1981
                     7566 JONES      02/04/1981
                     7902 FORD       03/12/1981
                     7900 JAMES      03/12/1981
                     7844 TURNER     08/09/1981
                     7782 CLARK      09/06/1981
                     7788 SCOTT      09/12/1982
                     7876 ADAMS      12/01/1983
                     7839 KING       17/11/1981
                     7369 SMITH      17/12/1980
                     7499 ALLEN      20/02/1981
                     7521 WARD       22/02/1981
                     7934 MILLER     23/01/1982
                     7654 MARTIN     28/09/1981

          14 rows selected.

          SQL> var d1 varchar2(20);
          SQL> var d2 varchar2(20);
          SQL> exec :d1 := '19/02/1981';

          PL/SQL procedure successfully completed.

          SQL> exec :d2 := '25/02/1981';

          PL/SQL procedure successfully completed.

          SQL> select * from v_emp
            2  where to_date(hiredate,'dd/mm/yyyy') between to_date(:d1,'dd/mm/yyyy')
            3  and to_date(:d2,'dd/mm/yyyy');

                    EMPNO ENAME      HIREDATE
          --------------- ---------- --------------------
                     7499 ALLEN      20/02/1981
                     7521 WARD       22/02/1981

          SQL>
          • 2. Re: Can you pass parameters to a view?
            Himanshu Kandpal
            HI ,

            you can create a parameterized VIEW or have a SQL in your procedure which you execute dynamically.

            create view myview
            as select * from t where x = userenv('client_info')
            /
            and then:
            SQL> exec dbms_application_info.set_client_info('my_data')
            SQL> select * from myview;


            Thanks
            • 3. Re: Can you pass parameters to a view?
              590545
              This might be very basic:


              I have used the system suggested by hkandpal to create a parameterized view
              All works well in PL/SQL develloper.
              (Both Setting the variable as retreiving the data, as long as I do it within one session)
              The variable can't seem to be transferred to between sessions. even if I commit it.

              I need to extract this info in excel (though VBA)
              When the data is set into the Client info the data seems to be lost when I select the data in the VBA code below.

              Can you set te CLIENT_INFO and do the SELECT command in one go?


              In VBA:
              sqlStr = "select * from msg.excel_delta1"
              With ActiveSheet.QueryTables.Add(connectionStr, Sheet1.Range("A4"), sqlStr)
              • 4. Re: Can you pass parameters to a view?
                MichaelS
                Can you set te CLIENT_INFO and do the SELECT command in one go?
                Create a helper function:
                SQL> create view myview
                as
                   select * from emp where ename = sys_context ('userenv', 'client_info')
                /
                View created.
                
                SQL> create or replace function set_ename (ename varchar2)
                   return integer
                as
                begin
                   dbms_application_info.set_client_info(ename);
                   return 1;
                end set_ename;
                /
                Function created.
                
                SQL> select empno, ename from myview where 1 = set_ename('SCOTT')
                /
                     EMPNO ENAME     
                ---------- ----------
                      7788 SCOTT     
                
                
                1 row selected.
                SQL> select empno, ename from myview where 1 = set_ename('JAMES')
                /
                     EMPNO ENAME     
                ---------- ----------
                      7900 JAMES     
                
                
                1 row selected.
                But still not sure, why a normal »WHERE clause« wouldn't suffice?
                • 5. Re: Can you pass parameters to a view?
                  590545
                  Thank you michaels,..
                  Seem exactly like the thing I need.

                  I'll go and try in mediately.

                  Why I need the parametrized view?
                  Because the query in the view is so BIG that it's at least un managable (if not impossible) from within VBA.
                  I did try to make a view that will return all values and then use the where statement to filter for the results I need, but that became terrable slow. (6min+)
                  whilst using the correct parameters inside the view "only" 1.5 minutes was sufficient.

                  Thank you for your help.
                  • 6. Re: Can you pass parameters to a view?
                    Billy~Verreynne
                    > Because the query in the view is so BIG that it's at least un managable (if
                    not impossible) from within VBA.

                    This does not make sense. Since when does a client language limitations dictates the design of the server?

                    How can VBA issues dictate that a "parameterised" view be used in Oracle?

                    It sounds more like an issue of not understanding how to use Oracle correctly.

                    > I did try to make a view that will return all values and then use the
                    where statement to filter for the results I need, but that became terrable
                    slow. (6min+) whilst using the correct parameters inside the view "only" 1.5
                    minutes was sufficient.

                    And this statement kinds of proves the point of not understanding Oracle.

                    There is NO difference between a SQL using a so-called parameterised view and a SQL using filters directly.. as in both cases the predicates will be applied by the SQL engine!

                    It is nonsensical to claim that one method is faster than the other - just as it would be to state that a car painted red is faster than a car painted blue.. (when everyone knows that green and gold is the best ;-) ).

                    It does not matter HOW you add your filter criteria to a SQL statement.. the SQL engine does that filtering. It cannot care whether you supplied that filter directly or indirectly using context variables.
                    • 7. Re: Can you pass parameters to a view?
                      590545
                      A)
                      It could very well be that I don't uderstand how to use oracle correctly.
                      I've never had a proper training, and consider myself a beginner.
                      So thank you for your help and positive input.

                      B)
                      I know the concept of how Oracle should find the best way to retreive the data you queried for. Somehow this doesn't always seem to be the case.
                      I've had a situation where a union would be faster than adding an extra OR statement.
                      But then again this could be caused by point A.

                      Anyway, thank you all for you help. I've got what I need.
                      (or shouldn't need, but prefer to use)
                      • 8. Re: Can you pass parameters to a view?
                        John Spencer
                        Billy:

                        With all due respect to the current WRC champions :-)

                        "There is NO difference between a SQL using a so-called parameterised view and a SQL using filters directly.. as in *both* cases the predicates will be applied by the SQL engine!"

                        I have run across several cases where "manually" pushing a predcate deep into a complex view gives significantly better response times than does applying filters to the view. There is a limit as to how far the optimizer can push a predicate.

                        A paramaterized view, or a stored proc returning a ref cursor is ideal in those situations.

                        Go 'Boks

                        John
                        • 9. Re: Can you pass parameters to a view?
                          Billy~Verreynne
                          Sorry John, I do not understand what you are saying.. what do you mean by "pushing a predicate"?

                          Yeah I know I'm slow.. but down here, we're all rough around the edges after Saturday's night win. :-)
                          • 10. Re: Can you pass parameters to a view?
                            John Spencer
                            Billy:

                            Completely understandable :-)

                            I don't have a shareable real case handy, but in concept I was trying to get at something like this (and yes, I realize there are better ways to write this particular case). Say you create a view like:
                            CREATE VIEW active_emps AS
                            SELECT emp.id, emp.name, emp.hire_date, job.title, job.rating
                            FROM (SELECT id, name, hire_date
                                  FROM emp
                                  WHERE termination_date is NULL) emp, job
                            WHERE emp.job_no = job.job_no
                            If you then query it like:
                            SELECT * FROM active_emps
                            WHERE id = 123
                            The optimizer can push the predicate on emp.id into the in-line view inside the view and actually run something more like:
                            SELECT emp.id, emp.name, emp.hire_date, job.title, job.rating
                            FROM (SELECT id, name, hire_date
                                  FROM emp
                                  WHERE termination_date is NULL and
                                        id = 123) emp, job
                            WHERE emp.job_no = job.job_no
                            However, if you add another level of nesting something like:
                            SELECT emp.id, emp.name, emp.hire_date, job.title, job.rating
                            FROM (SELECT id, name, hire_date
                                  FROM emp
                                  WHERE id IN (SELECT empid
                                               FROM emp_status
                                               WHERE status = 'ON LEAVE') and
                                        termination_date IS NULL) emp, job
                            WHERE emp.job_no = job.job_no
                            Then, the optimizer may not be able to push the emp.id predicate down into the select from emp_status where it will do the most good. For something like this, a parameterized view, along the lines of:
                            SELECT emp.id, emp.name, emp.hire_date, job.title, job.rating
                            FROM (SELECT id, name, hire_date
                                  FROM emp
                                  WHERE id IN (SELECT empid
                                               FROM emp_status
                                               WHERE status = 'ON LEAVE' and
                                                     empid = NVL(sys_context('userenv', 'client_info'), empid)) and
                                        termination_date IS NULL) emp, job
                            WHERE emp.job_no = job.job_no
                            can help.

                            Another case for parameterized views is when you have a selective predicate in a subselect or in-line view that you want to be able to vary, but either cannot, or don't wish to pull all of the way out to the main projection.

                            John
                            • 11. Re: Can you pass parameters to a view?
                              Billy~Verreynne
                              Ah... lift went all the way up and someone got out and to turn on the lights. Thanks John.
                              • 12. Re: Can you pass parameters to a view?
                                Billy~Verreynne
                                > Completely understandable :-)

                                You're a Kiwi John? I think the Kiwis is the only nation that can really understand what it means. :-)
                                • 13. Re: Can you pass parameters to a view?
                                  John Spencer
                                  "You're a Kiwi John?"

                                  Nope, Irish way back, but at least 3rd generation Canadian. Most Canadians are as passionate about hockey as you guys are about rugby, so I understand what winning a big match means.

                                  John
                                  • 14. Re: Can you pass parameters to a view?
                                    Billy~Verreynne
                                    Then you also talk about winning a world cup as a defining moment in your country's history? Does your crime rate also drop by something like 80% in the weeks following as the whole country celebrates? I can't see that happening in any Western nation.

                                    This is Africa John.. things are often just a tad different over here, and at other times, just damn weird. :-)
                                    1 2 Previous Next