Multi-criteria search query in Oracle PL/SQL with dynamic SQL

How to implement a multi-criteria search query in PL/SQL with dynamic SQL.

It is very frequent to implement a search form with multiple criteria, where each criteria can be independently enabled by the user, according to her needs.
The application will then need to perform a query on the database, taking into account all the criteria choosen by the user, that is perform a dynamic query. Here comes an example of how to implement a dynamic query in PL/SQL, using dynamic SQL.

The query could be implemented also with a static query, which takes into account all the possible criteria, and switch them on according to the value of some input parameters.
This static query would sadly perform very bad, since it makes the optimizer choose access paths for unnecessary criteria.

For example, given the following table

CREATE TABLE SUBJECT
  (
    SUBJECTID   NUMBER(38,0) NOT NULL ENABLE,
    SURNAME NVARCHAR2(400),
    NAME NVARCHAR2(400)
    DATEOFBIRTH TIMESTAMP (6),
    CONSTRAINT PK_SUBJECT PRIMARY KEY (SUBJECTID) );

the following procedure performs a static search for an user taking into account her surname, name and birthdate and putting results into ResCursor1.
Each criteria is switching on in the query according to whether the corresponding input parameter is not null.

create or replace
PROCEDURE SEARCHPHYSICALPERSONS (Surname    in nvarchar2 default null,
                                                       Name       in nvarchar2 default null,
                                                       BirthDate  in date default null
                                                       ResCursor1 out sys_refcursor) is

  pSurname    Subject.Surname%TYPE := Surname;
  pName       Subject.Name%TYPE := Name;
  pBirthDate  Subject.Dateofbirth%TYPE := BirthDate;

begin

  open ResCursor1 for
    select distinct
           a.subjectid,
           surname,
           name,
      from Subject a
     where (pSurname is null or TRIM(upper(a.surname)) like TRIM(upper(pSurname||'%')))
       and (pName is null or TRIM(upper(a.name)) = TRIM(upper(pName)))
       and (pBirthDate is null or a.Dateofbirth = pBirthDate);

end SearchPhysicalPersons;

The following approach will instead optimize the search taking into account only the necessary criteria, building a dynamic sql query which is being parsed at runtime by the DB engine (the query will use binding variables, since they are necessary for optimal performances).

create or replace
TYPE physicalPersonsType AS OBJECT
    ( subjectid NUMBER(38,0),
      surname VARCHAR2(400 CHAR),
      name VARCHAR2(400 CHAR),
      dateofbirth TIMESTAMP(6),
    );

 
CREATE OR REPLACE TYPE physicalPersonsTableType AS TABLE OF physicalPersonsType;

create or replace
FUNCTION readSearchPhysicalPersons(cur_hdl int) RETURN physicalPersonsTableType
PIPELINED IS
  subjectid SUBJECT.SUBJECTID%type;
  surname SUBJECT.SURNAME%type;
  name SUBJECT.NAME%type;
  dateofbirth SUBJECT.DATEOFBIRTH%type;
BEGIN

      dbms_sql.define_column(cur_hdl, 1, subjectid);
      dbms_sql.define_column(cur_hdl, 2, surname, 400);
      dbms_sql.define_column(cur_hdl, 3, name, 400);
      dbms_sql.define_column(cur_hdl, 4, dateofbirth);

  LOOP
  -- fetch a row
  IF dbms_sql.fetch_rows(cur_hdl) > 0 then

      -- fetch columns from the row
      dbms_sql.column_value(cur_hdl, 1, subjectid);
      dbms_sql.column_value(cur_hdl, 2, surname);
      dbms_sql.column_value(cur_hdl, 3, name);
      dbms_sql.column_value(cur_hdl, 4, dateofbirth);
   
   PIPE ROW (physicalPersonsType(subjectid, surname, name, dateofbirth));
  ELSE
        EXIT; 
  END IF;
  END LOOP;
  
  RETURN;
END;

create or replace
PROCEDURE SEARCHPHYSICALPERSONS (Surname    in nvarchar2 default null,
                                                       Name       in nvarchar2 default null,
                                                       BirthDate  in date default null,
                                                       ResCursor1 out sys_refcursor) is

                                                       pSurname    Subject.Surname%TYPE := Surname;
                                                       pName       Subject.Name%TYPE := Name;
                                                       pBirthDate  Subject.Dateofbirth%TYPE := BirthDate;

  stmt_str varchar2(4000);
  cur_hdl int;
  rows_processed int;
  commaFlag boolean := false;
begin

cur_hdl := dbms_sql.open_cursor; -- open cursor
stmt_str := 'SELECT DISTINCT A.SUBJECTID, SURNAME, NAME, DATEOFBIRTH'
   ||' FROM SUBJECT A'
   ||' WHERE 1=1';

if pSurname is not null then
  stmt_str := stmt_str || ' AND (TRIM(UPPER(A.SURNAME)) LIKE TRIM(UPPER(:pSurname ||''%'')))';
end if;

if pName is not null then
  stmt_str := stmt_str || ' AND (TRIM(UPPER(A.NAME)) = TRIM(UPPER(:pName)))';
end if;

if pBirthDate is not null then
  stmt_str := stmt_str || ' AND (A.DATEOFBIRTH = :pBirthDate)';
end if;

--uncomment this line to see the generated sql
--dbms_output.put_line(stmt_str);

dbms_sql.parse(cur_hdl, stmt_str, dbms_sql.native);

-- supply binds (bind by name)
if pSurname is not null then
  dbms_sql.bind_variable(cur_hdl, ':pSurname', pSurname);
end if;

if pName is not null then
  dbms_sql.bind_variable(cur_hdl, ':pName', pName);
end if;

if pBirthDate is not null then
  dbms_sql.bind_variable(cur_hdl, ':pBirthDate', pBirthDate);
end if;

rows_processed := dbms_sql.execute(cur_hdl); -- execute

open ResCursor1 for
select * from table(readSearchPhysicalPersons(cur_hdl));

END;