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?

What is the issue with the first code? It is quite common way to express optional parameters in SQL and its performance will be as good as the plain query. No need to introduce dynamic SQL here. — astentx, Nov 01 '21 at 09:23
One thing to keep in mind is that dynamic sql generates a lot more different sql statements, each of them will have to be parsed by the sql engine and an execution plan created. From a database perspective, the first option makes more sense: only one query and one execution plan is needed — Koen Lostrie, Nov 23 '21 at 08:42

score 1 · Accepted Answer · answered Oct 31 '21 at 12:20

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.

answered Oct 31 '21 at 12:20

Andrew Sayer

2,296
1
7
9

In this case, surely it's no more efficient or even less efficient than the `(:param1 IS NULL OR column1 = :param1)` evaluation? – David Min Nov 01 '21 at 10:30
1

It’s more efficient. Oracle has to create a plan that is valid for the SQL no matter what value is used in the bind variable. For your case, it won’t optimize the query using an index because it’s possible that the bind is null. In my case, it knows that it is filtering on an equality or it’s not filtering at all. – Andrew Sayer Nov 01 '21 at 11:59

score 0 · Answer 2 · answered Oct 31 '21 at 13:42

0

You can do it in an equivalent way without dynamic SQL:

BEGIN
  IF :param1 IS NOT NULL AND :param1 IS NOT NULL THEN
    OPEN :resultSetOut FOR
      SELECT *
      FROM   MYTABLE
      WHERE  COLUMN1=:param1
      AND    COLUMN2=:param2;
  ELSIF :param1 IS NOT NULL THEN
    OPEN :resultSetOut FOR
      SELECT *
      FROM   MYTABLE
      WHERE  COLUMN1=:param1;
  ELSIF :param2 IS NOT NULL THEN
    OPEN :resultSetOut FOR
      SELECT *
      FROM   MYTABLE
      WHERE  COLUMN2=:param2;
  ELSE
    OPEN :resultSetOut FOR
      SELECT *
      FROM   MYTABLE;
  END IF;
END;

answered Oct 31 '21 at 13:42

MT0

143,790
11
59
117

The example code I've shown is simplified. This if structure will be unfeasible if there are more optional parameters. The number of combinations is way too high. – David Min Nov 01 '21 at 10:31
1

@DavidMin Then you probably want to use `(:param1 IS NULL OR column1 = :param1)` rather than trying to use dynamic SQL. However, you are asking "Is there a way to construct a string and bind similar to the second way [without binding all the parameters] shown here?" This does it without binding all the parameters and, yes, it gets complicated with more parameters but you either include all the parameters (your first option) or you have to individually handle which parameters are included and omitted and then you end up with lots of branching `IF` statements. I don't see a middle ground. – MT0 Nov 01 '21 at 10:39

Dynamic SQL with dynamic bindings in PL/SQL

2 Answers2