Forum Stats

  • 3,853,631 Users
  • 2,264,247 Discussions
  • 7,905,419 Comments

Discussions

is null is not working in if statement in PLSQL

Sunny86
Sunny86 Member Posts: 255 Blue Ribbon
edited Jan 22, 2019 6:35AM in SQL & PL/SQL

Hello All,

is null is not working even if the select statement didnt bring any results. Count is working

X varchar2(100) = '';Xcnt number;beginselect XID into X from XTable where ID=1;-- It is working if i count it and change the condition to check if the Xcnt=0 then Xcnt>0 thenend;if X is NULLthen-----endif X is not nullthen---end;
Frank KulashAhmed HaroonSunny86

Answers

  • Saubhik
    Saubhik Member Posts: 5,853 Gold Crown
    edited Jan 22, 2019 6:02AM

    Check X not XID.

  • Sunny86
    Sunny86 Member Posts: 255 Blue Ribbon
    edited Jan 22, 2019 6:05AM

    Sorry. it is a typo error.

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Jan 22, 2019 6:35AM

    So is it working now?

    I've never encountered a situation where IS NULL doesn't work. I have encountered situations where variables/columns I expected to be null weren't.

    Ahmed Haroon
  • KayK
    KayK Member Posts: 1,734 Bronze Crown
    edited Jan 22, 2019 6:27AM

    I expect that your example is not the real thing, that didn't run.

    In which case your X has to be null ? If your select find nothing you get a no_data_found exception and your "if X is null" will not run.

    Something like this is running successfull

    < scott:[email protected] > declare  2      X  varchar2(100) := '';    --  you missed the :  3      Xcnt number;  4  begin  5      select ENAME into X from emp where empno = 7369;  6      -- It is working if i count it and change the condition to check if the Xcnt=0 then Xcnt>0 then  7      if X is NULL  8      then  9            dbms_output.put_line ('X is null');10      end if;11      if X is not null12      then13            dbms_output.put_line ('X is not null');14      end if;15  end;16  /X is not nullPL/SQL-Prozedur erfolgreich abgeschlossen.< scott:[email protected] > declare  2      X  varchar2(100) := '';  3      Xcnt number;  4  begin  5      select null into X from emp where empno = 7369;  6      -- It is working if i count it and change the condition to check if the Xcnt=0 then Xcnt>0 then  7      if X is NULL  8      then  9            dbms_output.put_line ('X is null');10      end if;11      if X is not null12      then13            dbms_output.put_line ('X is not null');14      end if;15  end;16  /X is nullPL/SQL-Prozedur erfolgreich abgeschlossen.
    Frank KulashAhmed HaroonSunny86
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,496 Red Diamond
    edited Jan 22, 2019 6:35AM

    Hi,

    Whenever you have a question, please post a complete test script that the people who want to help you can run to re-create the problem and test their ideas.  Include CRETE TABLE and INSERT statements for any tables needed, complete PL/SQL procedures or blocks, and code to run any procedures used.  Also post the output you expect, and an explanation, using specific examples, of how you get those results from that data.
    Always say what version of Oracle you're using (e.g. 12.2.0.2.0).
    See the forum FAQ:

    Sunny86Sunny86