Why this code is not going into IF block in PL/SQL?

Question

I have below PL/SQL code and this is not going into IF block. Why is this not going into the IF Block?

Appreciate your responses.

DECLARE
  p_datacenterid VARCHAR2(50) := '';
  p_dcid         VARCHAR2(50);
BEGIN
  dbms_output.put_line('test');
  -- Check if DataCenterId is null
  IF nvl(p_datacenterid, '') = '' THEN
      dbms_output.put_line('DataCenterID is empty');
      SELECT datacenterid
        INTO p_dcid
        FROM pod_tab
       WHERE url = 'dit3.ezlm.adp.com'
         AND rownum = 1;
  END IF;
END;
/

score 6 · Answer 1 · edited May 23 '17 at 11:58

6

It looks like you are getting tripped up on comparing null values. An empty string is converted by Oracle into Null in the background. See here for details.

The IF statement ends up resolving to IF null = null which is never true.

Something like this would work

IF p_DataCenterID  IS NOT NULL  THEN
   --assert the values are within a range
ELSE
   --look up the value
END IF;

edited May 23 '17 at 11:58

Community

1
1

answered Oct 20 '15 at 22:55

kevinskio

4,431
1
22
36

I tried the above IF also. Still not going into IF LOOP. – Rita Oct 21 '15 at 01:35
You don't have a loop in your code @Rita... so it's not really clear what you're asking. As Bob demonstrates below this answer works... so you're going to have to be more specific about what is incorrect. – Ben Oct 21 '15 at 06:52

Bob Jarvis - Слава Україні · Accepted Answer · 2015-10-21T10:58:13.053

In PL/SQL and the Oracle database the empty string ('') is the same as NULL, and thus you must test for it as you would for NULL by using the IS NULL test instead of = NULL or = ''. Try running the following:

declare
  p_DataCenterID   varchar2(50) := '';  -- NOTE: '' is the same as NULL
  p_dcID           varchar2(50);
BEGIN
  dbms_output.put_line( 'test');

  -- Check if DataCenterId is null

  IF p_DataCenterID IS NULL THEN
    dbms_output.put_line('DataCenterID is empty');

    SELECT DataCenterId
      INTO p_dcID  
      FROM Pod_Tab 
      WHERE URL = 'dit3.ezlm.adp.com' 
        AND ROWNUM = 1;
  END IF;
END;
/

This will print

test
DataCenterID is empty

Best of luck.

Why this code is not going into IF block in PL/SQL?

2 Answers2