Check If a Variable Is Null in Plsql

Check if a variable is null in plsql

if var is NULL then
  var :=5;
end if;

Oracle 11 PL/SQL: check variable for null, empty string and no result

You could only put the logic in the exception handler, and explicitly raise that exception on null:

 BEGIN
    SELECT some_value
      INTO my_variable
      FROM some_table
     WHERE somecheck = 123;

    IF my_variable IS NULL
    THEN
        RAISE no_data_found;
    END IF;
 EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        ##perform logic##
 END;

or get the same effect by excluding null results in the first place:

 BEGIN
    SELECT some_value
      INTO my_variable
      FROM some_table
     WHERE somecheck = 123
       AND some_value IS NOT NULL;

 EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        ##perform logic##
 END;

Not that null and an empty string ('') are the same thing so you don't need to test for both. But as you mentioned the column is actually a CLOB, you could could check for a not-null but empty CLOB by adding a check for dbms_lob.getlength(some_value) > 0, either to the query or before the explicit raise.

A third approach is to add a local function that performs the logic, and call that from both places in your original code.

Check variable is null in if condition

because you´re getting an exception as of no_data_found (insert into wont get any data here and this will raise the exception) and as though no return happens => null as return:

CREATE OR REPLACE FUNCTION func
RETURN varchar2 AS
myvar INT;
BEGIN
  select 1 into myvar  from dual where 1=2;
  IF  myvar  IS NULL THEN
    return 'n';
  ELSE
    return 'y';
  END IF;
-- This will execute now
exception 
   when no_data_found then
      return 'no_data_found';
   -- Just to note, when it has 2 rows+ that´s the other possible exception here
   when to_many_rows then
      return 'to_many_rows';
END;
/
SELECT func() FROM DUAL;

O/P

no_data_found

Dynamically check if a variable has value in PL SQL

PL/SQL doesn't have much in the way of reflection. There's certainly no equivalent of NAME_IN. I couldn't solve this with dynamic SQL but I have found a solution.

Here is a proecdure. It has three procedures. Note that they are all mandatory, but we can pass NULL in a parameter's slot. This of course is one of my objections to such "soft coding": it obfuscates the API. Describing a procedure is no longer sufficient to know what arguments it demands.

create or replace procedure do_something
    (p1 in varchar2
     , p2 in varchar2
     , p3 in varchar2)
is     
    args sys.dbms_debug_vc2coll;
begin
    args := new sys.dbms_debug_vc2coll(p1, p2, p3);

    for r in ( select s.varname, a.position
               from syscfg s
                    join user_arguments a 
                        on (s.procname = a.object_name
                            and s.varname = a.argument_name)
               where s.procname = 'DO_SOMETHING'
               and s.mandatory = 'Y' 
               order by a.position
               )
    loop
        if args(r.position) is null
        then
            raise_application_error(-20000, r.varname ||' cannot be null');       
        end if;        
    end loop;

    dbms_output.put_line('Procedure executed successfully!');
end;
/

The "dynamic" parameter check works by populating a collection with the parameters in signature order. We get the position of the configured parameters by joing a data dictionary view with our config table. We then use the position as an index to the array. Note that the collection takes strings. I declared all my parameters as Varchars, but you might need to cast dates or numbers.

So, yes, it is clunky, but "this quest of avoidance often leads towards [...] complication, convolution, and all-around unmaintainable code." :)

Here is the content of the config table:

SQL> select * from syscfg
  2  /

PROCNAME                       VARNAME                        M
------------------------------ ------------------------------ -
DO_SOMETHING                   P1                             Y
DO_SOMETHING                   P2                             Y
DO_SOMETHING                   P3                             N

SQL> 

So, let's roll!

SQL> set serveroutput on
SQL> exec do_something('A', 'Y', null)

Procedure executed successfully!

PL/SQL procedure successfully completed.

SQL> exec do_something('A', null, 'X')
BEGIN do_something('A', null, 'X'); END;

*
ERROR at line 1:
ORA-20000: P2 cannot be null
ORA-06512: at "APC.DO_SOMETHING", line 24
ORA-06512: at line 1

SQL> 

Looks good, but to prove there's nothing up my sleeve....

SQL> update syscfg
set mandatory = 'N'
where varname = 'P2'
/
  2    3    4  
1 row updated.

SQL> select * from syscfg
  2  /

PROCNAME                       VARNAME                        M
------------------------------ ------------------------------ -
DO_SOMETHING                   P1                             Y
DO_SOMETHING                   P2                             N
DO_SOMETHING                   P3                             N

SQL> exec do_something('A', null, 'X')

Procedure executed successfully!

PL/SQL procedure successfully completed.

SQL> 

---

Perhaps your clients are nutty enough to think this ultra flexiblility would be handy in other places. Well the good news is this solution could easily be extracted into a standalone procedure which takes the PROCNAME and the array as parameters.

How to compare null value in PLSQL

I think you want a NULL-safe comparison. In Oracle, you can use multiple conditions:

IF var1 <> var2 OR
   (var1 is null and var2 is not null) OR
   (var1 is not null and var2 is null)

Type table null value check

Force a single error value into the table type?
And then check for this value.

if (PROD_AMNT > INV_AMNT) then            

 v_tab_1 := table_type(PROD_AMNT, 'CURR');

else

 v_tab_1 := table_type(0, 'ERR');

end if;

How to select into a variable in PL/SQL when the result might be null?

You can simply handle the NO_DATA_FOUND exception by setting your variable to NULL. This way, only one query is required.

    v_column my_table.column%TYPE;

BEGIN

    BEGIN
      select column into v_column from my_table where ...;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_column := NULL;
    END;

    ... use v_column here
END;

PLSQL - Find two values are equal when they are NULL

Use IS NULL:

IF     (training_event_rec_.training_fee = course_rec_.course_fee OR
       (training_event_rec_.training_fee IS NULL AND course_rec_.course_fee IS NULL))
   AND (training_event_rec_.training_fee_unit = course_rec_.course_fee_unit OR
        (training_event_rec_.training_fee_unit IS NULL AND course_rec_.course_fee_unit IS NULL))
   AND (training_event_rec_.currency = course_rec_.currency OR
        (training_event_rec_.currency IS NULL AND course_rec_.currency IS NULL))
   AND (training_evaluation_temp_ = course_evaluation_temp_ OR
        (training_evaluation_temp_ IS NULL AND course_evaluation_temp_ IS NULL))
THEN
    RETURN FALSE;
ELSE
     RETURN TRUE;
END IF;

NOTE:

• You can also compare Nulls like NVL(COLUMN_A,'X') = NVL(COLUMN_B,'X') only if you are sure that COLUMN_A & COLUMN_B will not contain value 'X'. So that is not recommended approach.

---

Related Topics