Dynamic Oracle Query, without dynamic

Question

The title is indeed strange, but I will try to explain.

I have been programming Oracle (PL-SQL) in version 11G for several years. I recently moved to a new workplace, and in the first task I was required to write a function that accepts 5 variables as input, and it is possible that not all variables will be sent - maybe only one.

As part of the process, there is a very large query between lots of very large tables, so the more values there are in the input, the more the JOINS between the tables are realized and the indexes are used efficiently. Like

create function get_customer(p_ssn number, p_phone number, p_email varchar2, p_address varchar2, p_unique_id number number) return <type>
is
begin
select...
from A, B, C, D, E, F, G
JOIN ..
where ssn = p_ssn
and phone = p_phone ...
end;

The first way I thought to solve the task is by using dynamic SQL, so that we check for a certain input whether it is not NULL, and if so we can chain its AND to the query and at the end EXECUTE IMMEDIATE for the entire query Like

v_sql varchar2(4000);
v_sql := 'select...
    from A, B, C, D, E, F, G
    JOIN ..
    WHERE 1=1'

if p_phone is not null then
v_sql := v_sql || ' AND phone = p_phone'
end if;

etc..

But, my new team leader decided not to use dynamic SQL because it is complicated and can crash and other reasons. He suggested using NVL for each of the inputs, for example

select...
from A, B, C, D, E, F, G
JOIN ..
where ssn = nvl (p_ssn, a.ssn)
and phone = nvl(p_phone, b.phone) etc..

I was stunned. I thought a DBA team leader should understand a thing or two about efficiency and runtime. After I showed him the execution plan was really bad, and in particular that it took him a lot of time to finish his run, he told me to find another effective way without using dynamic SQL.

So, other suggestions How can the task be solved?

If your team leader thinks there is another effective way to do this, then ask him to write such function - he will fail. A proper written function with dynamic SQL will be less complicated than a huge static SQL considering all variables and combinations. And what does he mean by "it can crash"? — Wernfried Domscheit
– Wernfried Domscheit, Commented Oct 21, 2024 at 8:19
Please edit the question and provide a minimal reproducible example with: the CREATE TABLE and INSERT statements for your tables; an explanation of the logic that you are trying to implement; YOUR attempt(s) at a solution; and the EXPLAIN PLAN for your solutions. There is a very big difference between having a function where if you pass differing numbers of arguments then you select from different tables compared to a function where if you pass different numbers of arguments then you always select from the same tables but with different filters and it is unclear which you are trying to implement. — MT0
– MT0, Commented Oct 21, 2024 at 9:13
When you have more than just a few optional input variables, then you should consider to bind the values with BIND_VARIABLE in DBMS_SQL package — Wernfried Domscheit
– Wernfried Domscheit, Commented Oct 21, 2024 at 10:33
You could perhaps use SQL_MACRO. I know that it is basically dynamic SQL, but maybe your boss won't know any better. You could perhaps use SQL_MACRO for the components of the join (i.e. join to SQL_MACRO table functions) rather than build the whole query in one go. — ajz
– ajz, Commented Oct 21, 2024 at 12:37

MT0 · Accepted Answer · 2024-10-21 09:28:50Z

1

In your example, you are not varying which tables are joined depending to whether the arguments are set; instead you are always selecting the same columns from the same tables and applying different filters based on whether the arguments are set or unset.

In that case, pass NULL as the default value for the optional arguments and then use a static query and check if the optional arguments are unset with IS NULL so that you do not apply that filter:

CREATE FUNCTION get_customer(
  p_ssn       A.SSN%TYPE       DEFAULT NULL,
  p_phone     B.PHONE%TYPE     DEFAULT NULL,
  p_email     C.EMAIL%TYPE     DEFAULT NULL,
  p_address   D.ADDRESS%TYPE   DEFAULT NULL,
  p_unique_id E.UNIQUE_ID%TYPE DEFAULT NULL
) RETURN <type>
IS
BEGIN
  SELECT A.column1,
         B.column2,
         C.column3,
         D.column4,
         E.column5,
         F.column6,
         G.column7
  INTO   ...
  FROM   A
         INNER JOIN B ON (...)
         INNER JOIN C ON (...)
         INNER JOIN D ON (...)
         INNER JOIN E ON (...)
         INNER JOIN F ON (...)
         INNER JOIN G ON (...)
  WHERE  (p_ssn   IS NULL OR a.ssn = p_ssn)
  AND    (p_phone IS NULL OR b.phone = p_phone)
  ...;
END;

If you want to use dynamic SQL then don't concatenate the values into the query (as that is how you introduce SQL injection vulnerabilities into your code). Instead, use a parameterised query to pass the values into the query using bind variables:

v_sql varchar2(4000);
v_sql := 'SELECT A.column1,
         B.column2,
         C.column3,
         D.column4,
         E.column5,
         F.column6,
         G.column7
  FROM   A
         INNER JOIN B ON (...)
         INNER JOIN C ON (...)
         INNER JOIN D ON (...)
         INNER JOIN E ON (...)
         INNER JOIN F ON (...)
         INNER JOIN G ON (...)
  WHERE  1=1'

IF p_ssn IS NOT NULL THEN
  v_sql := v_sql || ' AND ssn = :1';
ELSE
  v_sql := v_sql || ' AND :1 IS NULL';
END IF;

IF p_phone IS NOT NULL THEN
  v_sql := v_sql || ' AND phone = :2';
ELSE
  v_sql := v_sql || ' AND :2 IS NULL';
END IF;

EXECUTE IMMEDIATE v_sql
  INTO ...
  USING p_ssn, p_phone;

answered Oct 21, 2024 at 9:28

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

MT0 Over a year ago

@WernfriedDomscheit The first half of the answer is not dynamic SQL. The second half of the answer starts with "If you want to use dynamic SQL ..." and gives an improvement on the solution the OP provides in the question so I'm unsure what point you are trying to make.

StevenU Over a year ago

the first half of his answer is bad approach, check it on execution plan. I've tried that also, it works, but taking a LONG time to run @MT0

MT0 Over a year ago

@StevenU We cannot check it as you have not provided a minimal reproducible example so we cannot replicate your tables. Please edit your question and provide the CREATE table and INSERT statements so we can replicate the issue.

StevenU Over a year ago

Forgive me, but I don't understand exactly why you need to replicate my tables. I think the subject of the discussion is clear enough, regardless of which tables or columns are used in the process, since we are trying to find and describ a solution to a general problem that may suit anyone, and any structure of tables

MT0 Over a year ago

@StevenU You are asking a performance question, part of that is to be able to generate an EXPLAIN PLAN so that the performance can be compared - we cannot do that if there is nothing to explain (i.e. the question is theoretical). You need to provide a minimal reproducible example that actually allows us to generate and compare EXPLAIN PLANs to generate a better plan.

Collectives™ on Stack Overflow

Dynamic Oracle Query, without dynamic

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related