RE: Useful Oracle books - C.J. Date theory vs. practicality

From: QuijadaReina, Julio C <QuijadJC_at_alfredstate.edu>
Date: Fri, 28 May 2004 19:12:35 -0400
Message-ID: <7091A5ABA356E04B8FA592067D78B539253A17@mail.alfredstate.edu>

Lex,

How about using: 'string' IS NULL

instead of
'string' = NULL?

19:05:15 SQL> set serveroutput on
19:05:22 SQL> @three_way_logic
19:05:32 SQL> begin
19:05:32   2      if     (&&condition) then
19:05:32   3             dbms_output.put_line('THEN branch');
19:05:32   4      else
19:05:32   5             dbms_output.put_line('ELSE branch');
19:05:32   6      end    if;
19:05:32   7      if not (&condition) then
19:05:32   8             dbms_output.put_line('THEN branch');
19:05:32   9      else
19:05:32  10             dbms_output.put_line('ELSE branch');
19:05:32  11      end if;
19:05:32  12  end;
19:05:32  13  /

Enter value for condition: 'Mort subite' is NULL ELSE branch
THEN branch

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
19:05:54 SQL>

From: oracle-l-bounce_at_freelists.org on behalf of Lex de Haan Sent: Fri 5/28/2004 3:56 PM
To: oracle-l_at_freelists.org
Subject: RE: Useful Oracle books - C.J. Date theory vs. practicality

Jared, I stored your example in a script, making it prompt for a condition:

SQL> @jared
Enter value for condition: 'Mort subite' = NULL ELSE branch
ELSE branch

PL/SQL procedure successfully completed.

SQL> @jared
Enter value for condition: 1=1
THEN branch
ELSE branch

PL/SQL procedure successfully completed.

SQL> list
1 begin

  2    if     (&&condition) then
  3           dbms_output.put_line('THEN branch');
  4    else
  5           dbms_output.put_line('ELSE branch');

  6 end if;
  7 if not (&condition) then
  8 dbms_output.put_line('THEN branch');   9 else
10 dbms_output.put_line('ELSE branch'); 11 end if;
12* end;
SQL> Kind regards,
Lex.

visit my website at http://www.naturaljoin.nl <http://www.naturaljoin.nl/>

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Powell, Mark D Sent: Friday, May 28, 2004 20:48
To: 'oracle-l_at_freelists.org'
Subject: RE: Useful Oracle books - C.J. Date theory vs. practicality

I think the point was if part of some condition was Null then the statement is not true, but is not false either as technically the result is unknown so in pl/sql you potentially should have a construct that looks like

If true

...
Else if false
...
Else [it is null so]

I have seen more than one bug traced to the failure of a developer to remember that in pl/sql just because something is not true that does not mean it is false. Failure to remember this results in attempts to find rows for the FALSE condition when there is no value to be used to query for those rows.

In native COBOL there is only True and False. However if you use COBOL with an RDBMS both Oracle and DB2 provide a null value indicator feature that can be used to allow program logic to properly handle the data. No matter what language is used when the data comes from an RDBMS that supports NULLs this issue of TRUE/ FALSE/ NULL probably exists.

Just adding to the noise level.
-- Mark D Powell --

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------




application/ms-tnef attachment: winmail.dat

Received on Fri May 28 2004 - 18:09:43 CDT