Stored Procedure PL SQL If String Is blank

Question

I got a problem with Oracle Stored Procedure. The if else statement didnt check whether the string is blank or not. Or am I doing it wrong?

create or replace PROCEDURE GET_ICECREAM
(
   flavour IN VARCHAR2,
   toppings IN VARCHAR2,
   cursorIC OUT sys_refcursor
)
AS

dynaQuery VARCHAR2(8000);

BEGIN

dynaQuery := 'SELECT price FROM tblIceCream';

IF flavour <> '' THEN

   dynaQuery := dynaQuery || ' WHERE flavour LIKE '''%''' '

ENDIF



OPEN cursorIC FOR dynaQuery;

END GET_ICECREAM;

DISCLAIMER: Above is not actual stored procedure. I'm using an example to understand concept of if else and native dynamic SQL in Oracle. So that its easier for you guys to understand ;)

Answer 1

Hope this below snippet will help you to understand how to handle empty string and NULL values.

SET serveroutput ON;
DECLARE
  lv_var VARCHAR2(100):='';
BEGIN
  IF lv_var IS NULL THEN
    dbms_output.put_line('is null');
  ELSE
    dbms_output.put_line('av');
  END IF;
END;

------------------------------------OUTPUT--------------------------------------

PL/SQL procedure successfully completed.

is null

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

SET serveroutput ON;
DECLARE
  lv_var VARCHAR2(100):='';
BEGIN
  IF lv_var = '' THEN
    dbms_output.put_line('is null');
  ELSE
    dbms_output.put_line('av');
  END IF;
END;

--------------------------------------output-----------------------------------
PL/SQL procedure successfully completed.

av

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

Answer 2

In PL/SQL, a zero length string that is assigned to a varchar2 variable is treated as a NULL.

In your case, if argument flavour is assigned a zero length string, the line below is actually comparing a NULL to something, which is always false.

IF flavour <> '' then

Fix that line according to your business logic to take care of null values for flavour, and you'll be fine. An example fix would be:

if flavour is not null then