Re: Evaluating both sides of OR?

From: Steve Bradshaw <sjb1970_at_googlemail.com>
Date: Thu, 16 Apr 2009 16:26:14 +0100
Message-ID: <a08b4a570904160826n51757c48h6d6d6f7c6b4bf255_at_mail.gmail.com>

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_at_inter.net.il> 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
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 16 2009 - 10:26:14 CDT