Dynamic SQL is a powerful tool. In its proper place it can achieve quite a bit of work in a relatively small amount of code. Used improperly, however, it can cause all sorts of code maintenance headaches.

Consider the following procedure. It uses the HR schema and sample data that comes with an installation of the Oracle database:

CREATE OR REPLACE procedure FOO.SEARCH_EMPLOYEES (I_DEPARTMENT_ID   in     number,
                                              I_MANAGER_ID      in     number,
                                              I_LOCATION_ID     in     number,
                                              I_COUNTRY_ID      in     varchar2,
                                              I_REGION_ID       in     number,
                                              O_CURSOR             out SYS_REFCURSOR) is
begin
   open O_CURSOR for
           'select   E.EMPLOYEE_ID, '
        || '         D.DEPARTMENT_NAME, '
        || '         M.FIRST_NAME || '' '' || M.LAST_NAME MANAGER_NAME, '
        || '         M.EMAIL MANAGER_EMAIL, '
        || '         M.PHONE_NUMBER MANAGER_PHONE_NUMBER, '
        || '         L.STREET_ADDRESS, '
        || '         L.POSTAL_CODE, '
        || '         L.CITY, '
        || '         L.STATE_PROVINCE, '
        || '         C.COUNTRY_NAME, '
        || '         R.REGION_NAME, '
        || '         E.FIRST_NAME || '' '' || E.LAST_NAME EMPLOYEE_NAME, '
        || '         E.EMAIL EMPLOYEE_EMAIL, '
        || '         E.PHONE_NUMBER EMPLOYEE_PHONE_NUMBER '
        || 'from          HR.DEPARTMENTS D '
        || '         join HR.EMPLOYEES M '
        || '           on M.EMPLOYEE_ID = D.MANAGER_ID '
        || '         join HR.LOCATIONS L '
        || '           on L.LOCATION_ID = D.LOCATION_ID '
        || '         join HR.COUNTRIES C '
        || '           on C.COUNTRY_ID = L.COUNTRY_ID '
        || '         join HR.REGIONS R '
        || '           on R.REGION_ID = C.REGION_ID '
        || '         join HR.EMPLOYEES E '
        || '           on E.DEPARTMENT_ID = D.DEPARTMENT_ID '
        || 'where '
        || case
               when I_DEPARTMENT_ID is not null then 'D.DEPARTMENT_ID = :I_DEPARTMENT_ID'
               else ':I_DEPARTMENT_ID is null'
           end
        || ' and '
        || case
               when I_MANAGER_ID is not null then 'D.MANAGER_ID = :I_MANAGER_ID'
               else ':I_MANAGER_ID is null'
           end
        || ' and '
        || case
               when I_LOCATION_ID is not null then 'L.LOCATION_ID = :I_LOCATION_ID'
               else ':I_LOCATION_ID is null'
           end
        || ' and '
        || case
               when I_COUNTRY_ID is not null then 'C.COUNTRY_ID = :I_COUNTRY_ID'
               else ':I_COUNTRY_ID is null'
           end
        || ' and '
        || case
               when I_REGION_ID is not null then 'R.REGION_ID = :I_REGION_ID'
               else ':I_REGION_ID is null'
           end
        using I_DEPARTMENT_ID,
              I_MANAGER_ID,
              I_LOCATION_ID,
              I_COUNTRY_ID,
              I_REGION_ID;
end SEARCH_EMPLOYEES;

Since dynamic queries are not parsed until run-time, it behoves us to write as little SQL as possible to avoid bugs in the code. I’ve seen dynamic query strings that ran for hundreds of lines with several joins, unions, aggregations, and parameter values hard-coded into the string (we all know better than to do that unless (a) there’s an extremely compelling reason and (b) we check for SQL injection attacks, right?). The more involved our SQL is, especially if our code conditionally adds clauses to the query, the greater likelihood for a typographical error in SQL that we may or may not come across in our testing.

The query in SEARCH_EMPLOYEES has several joins, aliases some columns, and concatenates some columns with blank spaces, necessitating some escaped single-quotes in the middle of our string (lines 11 and 20). This example query string is not too bad, but we’ll use it to illustrate a coding strategy that can do a lot to simplify our code development and maintenance.

First, we’ll define a view to handle the joins, column transformations, and aliasing. We want as much work as possible performed by the view so that the procedure only selects and filters–nothing more:

create or replace force view FOO.SEARCH_EMPLOYEES_VIEW (EMPLOYEE_ID,
                                                        DEPARTMENT_ID,
                                                        MANAGER_ID,
                                                        LOCATION_ID,
                                                        COUNTRY_ID,
                                                        REGION_ID,
                                                        DEPARTMENT_NAME,
                                                        MANAGER_NAME,
                                                        MANAGER_EMAIL,
                                                        MANAGER_PHONE_NUMBER,
                                                        STREET_ADDRESS,
                                                        POSTAL_CODE,
                                                        CITY,
                                                        STATE_PROVINCE,
                                                        COUNTRY_NAME,
                                                        REGION_NAME,
                                                        EMPLOYEE_NAME,
                                                        EMPLOYEE_EMAIL,
                                                        EMPLOYEE_PHONE_NUMBER) as
    select   E.EMPLOYEE_ID,
             D.DEPARTMENT_ID,
             D.MANAGER_ID,
             L.LOCATION_ID,
             C.COUNTRY_ID,
             R.REGION_ID,
             D.DEPARTMENT_NAME,
             M.FIRST_NAME || ' ' || M.LAST_NAME MANAGER_NAME,
             M.EMAIL MANAGER_EMAIL,
             M.PHONE_NUMBER MANAGER_PHONE_NUMBER,
             L.STREET_ADDRESS,
             L.POSTAL_CODE,
             L.CITY,
             L.STATE_PROVINCE,
             C.COUNTRY_NAME,
             R.REGION_NAME,
             E.FIRST_NAME || ' ' || E.LAST_NAME EMPLOYEE_NAME,
             E.EMAIL EMPLOYEE_EMAIL,
             E.PHONE_NUMBER EMPLOYEE_PHONE_NUMBER
    from          HR.DEPARTMENTS D
             join HR.EMPLOYEES M
               on M.EMPLOYEE_ID = D.MANAGER_ID
             join HR.LOCATIONS L
               on L.LOCATION_ID = D.LOCATION_ID
             join HR.COUNTRIES C
               on C.COUNTRY_ID = L.COUNTRY_ID
             join HR.REGIONS R
               on R.REGION_ID = C.REGION_ID
             join HR.EMPLOYEES E
               on E.DEPARTMENT_ID = D.DEPARTMENT_ID

Next, we’ll rewrite our stored procedure:

CREATE OR REPLACE procedure FOO.SEARCH_EMPLOYEES (I_DEPARTMENT_ID   in     FOO.SEARCH_EMPLOYEES_VIEW.DEPARTMENT_ID%type,
                                              I_MANAGER_ID      in     FOO.SEARCH_EMPLOYEES_VIEW.MANAGER_ID%type,
                                              I_LOCATION_ID     in     FOO.SEARCH_EMPLOYEES_VIEW.LOCATION_ID%type,
                                              I_COUNTRY_ID      in     FOO.SEARCH_EMPLOYEES_VIEW.COUNTRY_ID%type,
                                              I_REGION_ID       in     FOO.SEARCH_EMPLOYEES_VIEW.REGION_ID%type,
                                              O_CURSOR             out SYS_REFCURSOR) is
begin
    open O_CURSOR for
           'select   SEV.EMPLOYEE_ID, '
        || '         SEV.DEPARTMENT_NAME, '
        || '         SEV.MANAGER_NAME, '
        || '         SEV.MANAGER_EMAIL, '
        || '         SEV.MANAGER_PHONE_NUMBER, '
        || '         SEV.STREET_ADDRESS, '
        || '         SEV.POSTAL_CODE, '
        || '         SEV.CITY, '
        || '         SEV.STATE_PROVINCE, '
        || '         SEV.COUNTRY_NAME, '
        || '         SEV.REGION_NAME, '
        || '         SEV.EMPLOYEE_NAME, '
        || '         SEV.EMPLOYEE_EMAIL, '
        || '         SEV.EMPLOYEE_PHONE_NUMBER '
        || 'from     FOO.SEARCH_EMPLOYEES_VIEW SEV '
        || 'where '
        || case
               when I_DEPARTMENT_ID is not null then 'SEV.DEPARTMENT_ID = :I_DEPARTMENT_ID'
               else ':I_DEPARTMENT_ID is null'
           end
        || ' and '
        || case
               when I_MANAGER_ID is not null then 'SEV.MANAGER_ID = :I_MANAGER_ID'
               else ':I_MANAGER_ID is null'
           end
        || ' and '
        || case
               when I_LOCATION_ID is not null then 'SEV.LOCATION_ID = :I_LOCATION_ID'
               else ':I_LOCATION_ID is null'
           end
        || ' and '
        || case
               when I_COUNTRY_ID is not null then 'SEV.COUNTRY_ID = :I_COUNTRY_ID'
               else ':I_COUNTRY_ID is null'
           end
        || ' and '
        || case
               when I_REGION_ID is not null then 'SEV.REGION_ID = :I_REGION_ID'
               else ':I_REGION_ID is null'
           end
        using I_DEPARTMENT_ID,
              I_MANAGER_ID,
              I_LOCATION_ID,
              I_COUNTRY_ID,
              I_REGION_ID;
end SEARCH_EMPLOYEES;

Notice how much tidier the query is in this procedure. We’ve reduced the amount of text in the string by moving much of the SQL heavy-lifting into a view, reducing our risk of typoes. We’ve defined the parameters to be typed by columns in the SEARCH_EMPLOYEES_VIEW, which creates a hard dependency between the procedure and the view. The prior version of this procedure had no hard dependencies. One could drop an underlying table with impunity–until the procedure was called. With the new version of the query an alteration of the underlying view would invalidate the procedure, alerting us to the fact that a code object has been impacted by the schema change.