how to check if a record is null

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.

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 errErrors for PACKAGE BODY P:LINE/COL ERROR-------- -----------------------------------------------------------------4/9      PL/SQL: Statement ignored4/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>

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

CREATE OR REPLACE PACKAGE pAS  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 pAS  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;