Bad Hint

[PPA]

Error: Hint: Comparison with NULL in 'Bind'
Line: 60

Text: akt_date_death = null,

exception
when no_data_found then
update akt set
akt_date_death = null,

 

[hadfieldjohn]

Are you trying to ask a question? Or is this just for our information? If this is a question then please put *some* effort into your post.

The hint is actually telling you that in Oracle there is absolutely no point in putting the expression "= NULL" into an SQL statement. It will *never* evaluate to TRUE. One of the first things you should have learned in Oracle is that null never evaluates to anything except "IS NULL" and "IS NOT NULL".

You can actually also use NULL evaluations in the DECODE function as well, but that's really using NULL as an expression on its own.

Good luck!

J.

 

[patch]

I think PPA is trying to make a point here that the hints aren't always correct, since the statement he posted is an update of a table, setting values to null, where the hint-engine thinks it's an evaluation because of the = sign. Maybe the hint engine should check the source lines before the current line to see if it's in an DML statement.

regards,
Patrick

------------------
Check out: http://www.oracledeveloper.nl

 

[hadfieldjohn]

My bad. PPA is obviously correct.

I must have got out of the wrong side of bed this morning!

If he'd been a little more terse (or I'd read it more thoroughly) confusion would've been avoided.

Sorry.

J.

[This message has been edited by hadfieldjohn (edited 06 October 2003).]

 

[Marco Kalter]

I think this is an old bug. What is your PL/SQL Developer version?

------------------
Marco Kalter
Allround Automations

 

[PPA]

PL/SQL v 5.1.0.654

 

[Marco Kalter]

If you upgrade to the current 5.1.3 release this should be fixed.

------------------
Marco Kalter
Allround Automations

 

[Rick O]

Version 5.1.3.704 displays the "Comparison with NULL" warning for the following line:

IF status IS NULL OR type_code = '' THEN

The parser apparently just looks for the 'NULL' and '=', and ignores the context.

 

[patch]

the comparison type_code = '' is actually the same as a comparison to null. So imho the hint is correct.
check this anonymous block:

SET SERVEROUTPUT ON;
DECLARE
 lv_test VARCHAR2(200);
 PROCEDURE check_null(pi_varchar IN VARCHAR2)
 IS
 BEGIN
  IF pi_varchar IS NULL THEN
   dbms_output.put_line('variable is null');
  ELSE
   dbms_output.put_line('variable is not null');
  END IF;
 END;
BEGIN
 dbms_output.put_line('set var NULL');
 lv_test := NULL;
 check_null(lv_test);
 dbms_output.put_line('set var ''some value''');
 lv_test := 'Some value';
 check_null(lv_test);
 dbms_output.put_line('set var ''''');
 lv_test := '';
 check_null(lv_test);
END;
/

 

[Rick O]

Excellent point. That's what I get for trying to convert VB code to PL/SQL!