Dynamic SQL with dynamic bindings in PL/SQL

Question

I'm writing a REST handler in ORDS. The URL endpoint should allow queries to be made with a number of optional parameters. One way to write the PL/SQL code could be:

DECLARE
  cur SYS_REFCURSOR
BEGIN
  OPEN cur FOR
    SELECT * FROM MYTABLE WHERE
    (:param1 IS NULL OR column1 = :param1) AND
    (:param2 IS NULL OR column2 = :param2);
  :resultSetOut := cur;
END;

The other way that I thought could be slightly more performant is to construct the SQL string

DECLARE
  cur SYS_REFCURSOR
  sqlString VARCHAR2(200)
BEGIN
  sqlString := 'SELCT * FROM MYTABLE WHERE 1=1';
  IF (:param1 IS NOT NULL) THEN sqlString := sqlString || ' AND COLUMN1=:param1'; END IF;
  IF (:param2 IS NOT NULL) THEN sqlString := sqlString || ' AND COLUMN1=:param2'; END IF;
  OPEN cur FOR sqlString USING :param1, :param2;
  :resultSetOut := cur;
END;

However, this string construction at the end need to be statically bound to variables, which essentially makes all variables required and not optional in the URL query.

For a PL/SQL block that allows for dynamic WHERE clauses, is the ony way to use the first way shown here? Is there a way to construct a string and bind similar to the second way shown here?

Answer 1

You’re on the right track:

IF (:param1 IS NOT NULL) THEN sqlString := sqlString || ' AND COLUMN1=:param1'; 
Else sqlString := sqlString || ' and (1=1 or :param1 is null) ';
END IF;

Now you need the same amount of bind variables no matter what and the Oracle optimizer will know that 1 is always 1 so it doesn’t need to consider the other predicate.