Dynamic Oracle Query, without dynamic

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?

4

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;

5

Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa Dịch vụ tổ chức sự kiện 5 sao Thông tin về chúng tôi Dịch vụ sinh nhật bé trai Dịch vụ sinh nhật bé gái Sự kiện trọn gói Các tiết mục giải trí Dịch vụ bổ trợ Tiệc cưới sang trọng Dịch vụ khai trương Tư vấn tổ chức sự kiện Hình ảnh sự kiện Cập nhật tin tức Liên hệ ngay Thuê chú hề chuyên nghiệp Tiệc tất niên cho công ty Trang trí tiệc cuối năm Tiệc tất niên độc đáo Sinh nhật bé Hải Đăng Sinh nhật đáng yêu bé Khánh Vân Sinh nhật sang trọng Bích Ngân Tiệc sinh nhật bé Thanh Trang Dịch vụ ông già Noel Xiếc thú vui nhộn Biểu diễn xiếc quay đĩa Dịch vụ tổ chức tiệc uy tín Khám phá dịch vụ của chúng tôi Tiệc sinh nhật cho bé trai Trang trí tiệc cho bé gái Gói sự kiện chuyên nghiệp Chương trình giải trí hấp dẫn Dịch vụ hỗ trợ sự kiện Trang trí tiệc cưới đẹp Khởi đầu thành công với khai trương Chuyên gia tư vấn sự kiện Xem ảnh các sự kiện đẹp Tin mới về sự kiện Kết nối với đội ngũ chuyên gia Chú hề vui nhộn cho tiệc sinh nhật Ý tưởng tiệc cuối năm Tất niên độc đáo Trang trí tiệc hiện đại Tổ chức sinh nhật cho Hải Đăng Sinh nhật độc quyền Khánh Vân Phong cách tiệc Bích Ngân Trang trí tiệc bé Thanh Trang Thuê dịch vụ ông già Noel chuyên nghiệp Xem xiếc khỉ đặc sắc Xiếc quay đĩa thú vị
Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa
Thiết kế website Thiết kế website Thiết kế website Cách kháng tài khoản quảng cáo Mua bán Fanpage Facebook Dịch vụ SEO Tổ chức sinh nhật