variable in a view

Imagine you have a view and you want to have a parameter in your view. You cannot have a bind variable in your view. But you could have a function which return a package variable. And this package variable could be set manually for your session

Here we go

SQL> create or replace package p is n number; end p;
  2  /

Package created.

SQL> create or replace function f return number is 
  2  begin return p.n; end;
  3  /

Function created.

SQL> create or replace view v as select ename from 
  2  emp where empno=f;

View created.

SQL> select * from v;

no rows selected

SQL> exec p.n:=7788

PL/SQL procedure successfully completed.

SQL> select * from v;

ENAME
----------
SCOTT

13 thoughts on “variable in a view”

  5. Paweł Barut

    I’ve been using this technique for year. Everything started in Oracle 8 when pushing predicates into complex view didn’t work as expected. It solved many performance problems.

  6. Chris

    Stuff like this is very often used in “thick DB” or “database centric” approaches which I find very promising. Consider for example the papers of Toons Koppelaars (http://web.inter.nl.net/users/T.Koppelaars/).

    Using contexts will probably be faster, because it doesn’t involve a context switch to pl/sql and the optimizer can make better use of it. If you use a function, you might want to say it’s deterministic, btw.

  8. Chris

    Thanks for mentioning that I am mistaken, Laurent. I was under the impression that the “deterministic” clause refers to the time span of running a sql statement, which is nonsense. You live and learn, e.g. not to rely on heresay but read the docs and test 😉

  9. laurentschneider Post author

    by the way I am reading your document with great interest. I am currently in a J2EE project where the database has no importance for the developers. So your document give me some lights about how to argue that there is something more than select/insert/update in an Oracle Database. I wonder if I ever will write about this. For the moment it is more political than technical, so now no post about hibernate and OptimalJ 👿

  10. Chris

    Glad that you’re interested. The papers of Paul Dorsey (http://www.dulcian.com/Papers%20by%20topic/Methodology.htm) could help you in this discussion, too. The “The Thick Database Approach – Revisited” paper for example compares the development time / LOC of Java centric vs. database centric solutions. I suppose you will like the conclusion:

    “In our experience, moving code from the middle tier to the database had the following benefits:

    * Reduced the total amount of code
    * Reduced development time
    * Improved performance
    * Reduced network traffic
    * Reduced the complexity of the application

    Therefore, the thick database approach is a viable alternative to the conventional wisdom of reducing reliance on the database. It leverages existing database talent and can result in dramatic improvements in performance. The current trend in moving logic to the middle tier may have been premature given the experiences detailed in this paper.”

  11. Mathias

    I agree that this can be done. I’m missing something though. Why would you, why not just push predicate to the view when the view merge occurs (basically just using a where with this in the SQL that accesses the view)?

    That would be more effective as just one call needs to be executed. I’m sure this is useful somehow, but I’m not really sure when.

  12. laurentschneider Post author

    it makes the view somehow dynamic, for example if the view is selected from a reporting tool like BO, you can hide the logic.
    I have never used it myself

  13. andrew

    Mathias: predicates can’t be pushed past agregating clauses like union, distinct, count, sum etc. (see Pawel’s comment above). For performance you usually want to get the filtering done at the earliest stage. Oracle fine grained access (FGAC) uses the “context” approach.

Comments are closed.