Please consider the following script:

create or replace function f(p_limit in integer) return integer as
  set_global_context ('limit', p_limit);
  return p_limit;

create view v as 
select level as val from dual connect by level<=sys_context('global_context','limit');

select f(2), v.* from v;

F(2)                   VAL                    
---------------------- ---------------------- 
2                      1                      
2                      2                      

select f(4), v.* from v;

F(4)                   VAL                    
---------------------- ---------------------- 
4                      1                      
4                      2                      
4                      3                      
4                      4                      

Can I rely on f(x) being executed before the context is read inside the view, as it has been in this test case run on 10.2?

  • Can't help but think a login trigger might be more appropriate (if the level will always be the same, that is) – Philᵀᴹ Jul 28 '12 at 14:55
  • @Phil this is just an example - I'm using sys_context to parametrise a view and the param will be different each time. If you know of a way to set a global context from SQL without messing around like this I'd also be interested to hear that! – Jack Douglas Jul 28 '12 at 15:34
  • 1
    @JackDouglas: parameteraising a view is an idea that does not "feel" right to me. Under MSSQL what you are trying to do could be done using a user defined function that returns a resultset (rather than a value), - you can then SELECT stuff FROM dbo.FuncReturningTable(param) or similar. Oracle probably has equivalent functionality. Though if using this over large data sets I'd be careful to monitor performance: I'm not sure how bright the query planner would need to be to make an efficient plan from such syntax. – David Spillett Jul 29 '12 at 11:48
  • @David parameteraising a view is commonly done with sys_context - just normally you would set the context prior to executing the query (eg with a bit of PL/SQL). Oracle has set-returning and/or pipelined functions but they are not the 'normal' way of achieving this. To be clear, I think the answer to the question in the title is "no" - I just wondered if someone knew better. – Jack Douglas Jul 29 '12 at 20:02


If you re-write your view with the context filtering against the where clause (instead of the connect by), you'll get the previously set value for the context:

create table t as 
 select rownum r from dual connect by level <= 10;

create or replace view v as 
  select r val from t where r <=sys_context('global_context','limit');

select f(2), v.* from v;

F(2) VAL
---- ---
   2   1 
   2   2 

select f(4), v.* from v;

F(4) VAL
---- ---
   4   1 
   4   2 

select f(4), v.* from v;

F(4) VAL
---- ---
   4   1 
   4   2 
   4   3 
   4   4 

As the where clause is evaluated before the columns are selected, the value passed to the function isn't set until after the context is read. The location of the sys_context call in your query (select, where, group by, etc.) will affect exactly when this value is set.

  • +1 that's pretty much "case closed" in my book, thanks. – Jack Douglas Jul 30 '12 at 16:01

Generally speaking you can not safely assume anything about the order in which your DBMS will do things when evaluating a single SQL statement. This is why many DBMS will not allow functions used that way to have side effects (i.e. MSSQL won't allow functions to set global/connection state, which you are doing there, or alter table contents). A series of statements must be executed in a way that makes sense from one step to the next (i.e. they are run serially, or in such a way as you can't tell they were not), but within a single statement the query planner has free reign as long it doesn't introduce ambiguity where it does not already exist (in your example ambiguity already exists because the function has a side effect affects the view).

If the query planner were being bright enough to detect that the view is affected by the side-effects of the function, what would it do if you joined in another view that called that function potentially with different input values? It could quite quickly get very hairy - this sort of thing is why generally, in any programming context, functions should not have effects beyond their own output.

In this specific example I would say that it is unlikely that f(x) will be called first, as it is int he "display" part of the statement: the result set from the view is likely to be retrieved before any functions within the list of columns to return are evaluated. Of course this will vary depending on the DBMS used: I'm no Oracle expert and your test results show that the function does seem to be being called first in these instances. But I would be wary of relying on execution order within any single SQL statement all the same - even if it always works the way you expect right now it may not do so in future revisions (unless it is officially documented somewhere that execution will always go this way around).

  • 2
    Good answer, but I feel that Jack is looking for a definitive technical Oracle answer. – Philᵀᴹ Jul 29 '12 at 13:13

The documentation only promises that "The optimizer first evaluates expressions and conditions containing constants as fully as possible." (10.2, 11.2). You're not guaranteed that it will evaluate any particular expression first, or that it won't change that order from time to time (a new patchlevel within the same release?).

  • +1 excellent, thanks (though to my reading those docs don't quite square with Chris's answer) – Jack Douglas Jul 30 '12 at 16:00
  • 1
    The difference is whether the function is called in the where, select or some other clause. Functions in the select section won't affect the optimizer decisions (unless it's a subquery), so these don't need to be evaluated until fetching the results. Functions in the where clause will affect the join method used though, so need to be evaluated as far as possible as soon as possible. – Chris Saxon Jul 30 '12 at 19:40
  • @Chris is that experience speaking or did you get that from the docs somewhere? – Jack Douglas Jul 30 '12 at 19:46
  • I can't find a doc reference. Based on my experience, if called in the where clause to filter a single table it'll be accessed for every row (assuming a FTS), but only for the rows returned if in the select list. As the execution plan is set during parsing, this implies that functions in select can't affect it. A test case to check this could be done by creating a function setting a counter (in a package or table) and comparing the output based on where in the query it's placed. – Chris Saxon Jul 30 '12 at 20:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.