CODEWITHSUNDEEP
sqlif-statementstored-proceduresoracle11g
user2482822
user2482822

Reputation: 197

Oracle Stored Procedure - How to compare type NUMBER with intergers?

I have following stored procedure. Parameter FLAG_ passed in is only allowed to accept null, 0 or 1. But the condition checking (FLAG_ != 0 OR FLAG_ != 1) doesn't work. I guess this is because type for FLAG_ is NUMBER which include float. Is there a way to compare NUMBER to INT ?

create or replace PROCEDURE "ADD_RMV_FLAG"
(
  TEXT OUT VARCHAR2 
, FLAG_ IN NUMBER -- empty, 0 and 1 only values accepted.
) AS

BEGIN
-- input pramameters checking
IF FLAG_ is not null
THEN
   IF (FLAG_ is not null AND (FLAG_ != 0 OR FLAG_ != 1))
   THEN
     raise_application_error(-20001, 'ERROR: only empty, 0 or 1 is accepted   
     for FLAG. Passed in ' || FLAG_);
   END IF;
 END IF;
END ADD_RMV_FLAE

Upvotes: 0

Views: 699

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

This expression:

IF (FLAG_ is not null AND (FLAG_ != 0 OR FLAG_ != 1))

will always evaluate to TRUE. If flag is 0, then you get "false OR true". If flag is 3.1415926535 . . . , then you get "true or true".

You want AND, or better yet: NOT IN:

IF (FLAG_ is not null AND FLAG_ NOT IN (0, 1))

Upvotes: 1

Related Questions