how to check if a record is null

Question

I have a procedure which receives a record as an IN parameter (the procedure also has an OUT parameter.)

CREATE OR REPLACE PACKAGE p
  PROCEDURE p_select( p_filter_params IN t_filter_params
                    , p_order_list OUT SYS_REFCURSOR);
END p;

CREATE OR REPLACE PACKAGE BODY p
  PROCEDURE p_select( p_filter_params IN t_filter_params
                    , p_order_list OUT SYS_REFCURSOR) AS
  BEGIN
    IF p_filter_params IS NULL
      THEN
        NULL;
    END IF;
  END p_select,
END p;

executable section

DECLARE
  TYPE t_filter_params IS RECORD
    ( f_name    customers.cust_first_name%TYPE
    , l_name    customers.cust_last_name%TYPE
    , city      customers.cust_address.city%TYPE
    , from_date orders.order_date%TYPE
    , to_date   orders.order_date%TYPE);

  vr_params t_filter_params;

  TYPE ref_cursor IS REF CURSOR;

  v_cursor ref_cursor;

BEGIN
  /*vr_params.f_name := 'john';
  vr_params.l_name := 'smith';
  vr_params.city := 'Dallas';
  vr_params.from_date := SYSDATE - 100;
  vr_params.to_date := SYSDATE;*/

  --p.p_select(vr_params, v_cursor);
  p.p_select(null, v_cursor);
end;

Can someone tell me how to make it work the IF statement from procedure, please?

Thank you very much, Mikcutu.

Answer 1

Records are somehow special. If you set a record to null all the record properties are set to null, but the record itself still exists.

The first thing that comes to my mind is either checking if some sort of id is null on the record - that would mean the record is empty.

You don't have an id, but this could lead to another solution: adding another property which is null whenever it is not filled.

The third approach: just go through every property and check whether every property is null.

And finally, the best, reject using RECORD and use OBJECT instead: it works exactly as you expect it to work.

Answer 2

I believe you can not:

SQL> create or replace package p is
  2      type tType is record ( a number, b number);
  3      --
  4      procedure proc(pIn IN tType);
  5  end;
  6  /

Package created.

SQL> create or replace package body p is
  2      procedure proc(pIn IN tType) is
  3      begin
  4          if pIn is null then
  5              dbms_output.put_line('NULL');
  6          else
  7              dbms_output.put_line('NOT NULL');
  8          end if;
  9      end;
 10  end;
 11  /

Warning: Package Body created with compilation errors.

SQL> sho err
Errors for PACKAGE BODY P:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/9      PL/SQL: Statement ignored
4/12     PLS-00306: wrong number or types of arguments in call to 'IS
         NULL'

With a slightly different approach, you could use a schema-level type:

SQL> create or replace type tType is object ( a number, b number);
  2  /

Type created.

SQL> create or replace package p2 is
  2      --
  3      procedure proc(pIn IN tType);
  4  end;
  5  /

Package created.

SQL> create or replace package body p2 is
  2      procedure proc(pIn IN tType) is
  3      begin
  4          if pIn  is null then
  5              dbms_output.put_line('NULL');
  6          else
  7              dbms_output.put_line('NOT NULL');
  8          end if;
  9      end;
 10  end;
 11  /

Package body created.

SQL>

Answer 3

Don't complicate things passing a record, just pass the individual parameters:

CREATE OR REPLACE PACKAGE p
AS
  PROCEDURE p_select(
    in_f_name    customers.cust_first_name%TYPE
  , in_l_name    customers.cust_last_name%TYPE
  , in_city      customers.cust_address.city%TYPE
  , in_from_date orders.order_date%TYPE
  , in_to_date   orders.order_date%TYPE
  , out_order_list OUT SYS_REFCURSOR
  );
END p;

CREATE OR REPLACE PACKAGE BODY p
AS
  PROCEDURE p_select(
    in_f_name    customers.cust_first_name%TYPE
  , in_l_name    customers.cust_last_name%TYPE
  , in_city      customers.cust_address.city%TYPE
  , in_from_date orders.order_date%TYPE
  , in_to_date   orders.order_date%TYPE
  , out_order_list OUT SYS_REFCURSOR
  ) AS
  BEGIN
    OPEN out_order_list FOR
    SELECT *
    FROM   orders o INNER JOIN customers c ON ( o.customer_id = c.customer_id )
    WHERE  ( in_f_name IS NULL OR c.cust_first_name = in_f_name )
    OR     ( in_l_name IS NULL OR c.cust_last_name  = in_l_name )
    OR     ( in_city   IS NULL OR c.cust_address.city = in_city )
    OR     ( in_from_date IS NULL OR o.order_date >= in_from_date )
    OR     ( in_to_date   IS NULL OR o.order_date <= in_to_date );
  END p_select,
END p;

Answer 4

One solution would be to work with reverse logic. It is e.g. not possible to ask:

IF my_record IS NULL THEN
   do something;
END IF;

or

IF my_record.my_attibute IS NULL THEN
   do something;
END IF;

Instead it is possible to ask:

IF my_record.my_attibute IS NOT NULL THEN
   go on processing;
ELSE
   do something;
END IF;