Re: Evaluating both sides of OR?
- From: Steve Bradshaw <sjb1970@xxxxxxxxxxxxxx>
- To: adar666@xxxxxxxxxxxx
- Date: Thu, 16 Apr 2009 16:26:14 +0100
Hi,
Just tried this out on Oracle XE - seems to depend on the order of the
statements.
If I did the ":v1 is null" check first, works fine, if I did the "f(:v1)"
the function is invoked (and hits the raise_application_error).
Steve
SQL> create table test_t
2 (a varchar2(10)
3 ) ;
Table created.
SQL> create or replace function TEST_F(P_1 varchar2) return varchar2 is
2 begin
3 if P_1 is null
4 then
5 RAISE_APPLICATION_ERROR(-20666, 'DOH!');
6 end if;
7 return(P_1);
8 end;
9 /
Function created.
SQL> insert into TEST_T values ('a') ;
1 row created.
SQL> insert into TEST_T values (null) ;
1 row created.
SQL> set serveroutput on
SQL> edit
Wrote file afiedt.buf
1 declare
2 L_VAR varchar2(10) := null;
3 begin
4 for each in (select NVL(A,'*') A
5 from TEST_T
6 where (L_VAR is null or TEST_F(L_VAR) is null)) loop
7 DBMS_OUTPUT.PUT_LINE(each.A);
8 end loop;
9* end;
SQL> /
a
*
PL/SQL procedure successfully completed.
SQL> edit
Wrote file afiedt.buf
1 declare
2 L_VAR varchar2(10) := null;
3 begin
4 for each in (select NVL(A,'*') A
5 from TEST_T
6 where (test_f(L_VAR) is null OR L_VAR is null)) loop
7 DBMS_OUTPUT.PUT_LINE(each.A);
8 end loop;
9* end;
SQL> /
declare
*
ERROR at line 1:
ORA-20666: DOH!
ORA-06512: at "TEST.TEST_F", line 5
ORA-06512: at line 4
SQL>
On Thu, Apr 16, 2009 at 2:52 PM, Yechiel Adar <adar666@xxxxxxxxxxxx> wrote:
> I seem to remember that oracle is evaluating both sides of OR in where
> clause.
>
> select a,b from c where a =1 and (:v1 is null or function(:v1) = b);
>
> Is the function activated when :v1 is null?
>
> --
> Adar Yechiel
> Rechovot, Israel
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
