SQL Macro Part2 column_value = nvl(:param, column_value)

Hi,

Since I am working on “SQL Macro”, I am keep testing things. so please see my previous post: https://mustafakalayci.me/2020/10/28/sql-macro-is-finally-here/

as mentioned in previous post, is it possible to get rid of column_value = nvl(:param, column_value) conditions? since they become a huge pain in big and complex queries. I provided a very cumbersome solution in previous post., I wanted to test and do more about it.

As I said I wasn’t able to read any parameter value in sql macro except numbers. So today I tried COLLECTIONS (Array) and got different results. Let’s start with COLUMNS. we can send a column list using DBMS_TF package:

create or replace function sf_group_any_Table (p dbms_Tf.table_t, p_columns dbms_tf.columns_t) return varchar2 sql_macro as
begin
  return 'select '||p_columns(1)||', count(*) from p ' ||
         ' group by  ' || p_columns(1);
end;
/

select * from sf_group_any_Table(hr.employees, columns(department_id));

DEPARTMENT_ID   COUNT(*)
------------- ----------
           50         45
           40          1
          110          2
           90          3
           30          6
           70          1
                       1
           10          1
           20          2
           60          5
          100          6
           80         34

select * from sf_group_any_Table(hr.departments, columns(location_id));

LOCATION_ID   COUNT(*)
----------- ----------
       1700         21
       1400          1
       2400          1
       1500          1
       1800          1
       2500          1
       2700          1

create or replace function sf_group_any_Table (p dbms_Tf.table_t, p_columns dbms_tf.columns_t) return varchar2 sql_macro as

begin

return 'select '||p_columns(1)||', count(*) from p ' ||

' group by ' || p_columns(1);

end;

select * from sf_group_any_Table(hr.employees, columns(department_id));

DEPARTMENT_ID COUNT(*)

------------- ----------

50 45

40 1

110 2

90 3

30 6

70 1

10 1

20 2

60 5

100 6

80 34

select * from sf_group_any_Table(hr.departments, columns(location_id));

LOCATION_ID COUNT(*)

----------- ----------

1700 21

1400 1

2400 1

1500 1

1800 1

2500 1

2700 1

so we are able to group any table based on a column and get count of it. since P_Columns is an array, we can send more columns in it but how can we use them dynamically in sql macro?

create or replace function sf_group_any_Table (p dbms_Tf.table_t, p_columns dbms_tf.columns_t) return varchar2 sql_macro as
  my_cols varchar2(4000);
begin
  /* beaware of SQL INJECTION */ 
  for i in 1..p_columns.count loop
    my_cols := my_cols || p_columns(i) || ',';
  end loop;
  my_cols := rtrim(my_cols, ',');
  return 'select '||my_cols||', count(*) from p ' ||
         ' group by  ' || my_cols;
end;
/

select * from sf_group_any_Table(hr.employees, columns(department_id, last_name));

DEPARTMENT_ID LAST_NAME                   COUNT(*)
------------- ------------------------- ----------
           60 Hunold                             1
           30 Himuro                             1
           50 Markle                             1
...
...

select * from sf_group_any_Table(hr.departments, columns(location_id, manager_id));


LOCATION_ID MANAGER_ID   COUNT(*)
----------- ---------- ----------
       2400        203          1
       1700                    16
       1700        200          1
       1700        114          1
       1800        201          1
       1500        121          1
       1700        108          1
...
...

create or replace function sf_group_any_Table (p dbms_Tf.table_t, p_columns dbms_tf.columns_t) return varchar2 sql_macro as

my_cols varchar2(4000);

begin

/* beaware of SQL INJECTION */

for i in 1..p_columns.count loop

my_cols := my_cols || p_columns(i) || ',';

end loop;

my_cols := rtrim(my_cols, ',');

return 'select '||my_cols||', count(*) from p ' ||

' group by ' || my_cols;

end;

select * from sf_group_any_Table(hr.employees, columns(department_id, last_name));

DEPARTMENT_ID LAST_NAME COUNT(*)

------------- ------------------------- ----------

60 Hunold 1

30 Himuro 1

50 Markle 1

...

select * from sf_group_any_Table(hr.departments, columns(location_id, manager_id));

LOCATION_ID MANAGER_ID COUNT(*)

----------- ---------- ----------

2400 203 1

1700 16

1700 200 1

1700 114 1

1800 201 1

1500 121 1

1700 108 1

...

great we can use plsql to generate column list and use it in sql statement but of course this could be a little bit dangerous because we started to use “string concatenation”. Not much but a little bit because columns in DBMS_TF.COLUMS_T array must be actual columns of the previous parameter or you got an error.

since we can use collections and data is passed to plsql (unlike varchar2 parameter) may be we can use user defined collections to pass a parameter and we can! that is really excited me at first(not then).

So since I wanted to eliminate unnecessary conditions like ” column1 = nvl(:param1, column1″ if :param1 is null, I planned to pass those column name and parameter value and check them in sql macro. if parameter value is not null then add it to sql condition.

here is VERY basic version:

Create Type T_Condition As Object(
  Condition_Column_Name Varchar2(128),
  Condition_Value Varchar2(4000) -- not used column_value because 
);
/

Create Type T_Arr_Condition Is Table Of T_Condition;
/

Create Or Replace Function Sf_Generate_Sql (P Dbms_Tf.Table_T, P_Columns Dbms_Tf.Columns_T, P_Where T_Arr_Condition) Return Varchar2 Sql_Macro As
  My_Cols Varchar2(4000);
  Mycondition Varchar2(4000) ;
Begin
  /* be aware of injections! */
  For I In 1..P_Columns.Count Loop
    My_Cols := My_Cols || P_Columns(I) || ',';
  End Loop;
  
  For I In 1..P_Where.Count Loop
    If P_Where(I).Condition_Value Is Not Null Then
      Mycondition := Mycondition || P_Where(I).Condition_Column_Name || ' = ' || P_Where(I).Condition_Value || ' and ';
    End If;
  End Loop;
  Mycondition := Substr(Mycondition, 1, Length(Mycondition)-4);
  
  If Mycondition Is Not Null Then
    Mycondition := ' where '|| Mycondition ;
  End If;
  
  My_Cols := Rtrim(My_Cols, ',');
  
  Return 'select '||My_Cols||' from p ' ||Mycondition;
  
End;
/

set autotrace on

Select * 
From Sf_Generate_Sql(Hr.Employees, 
                     Columns(Employee_Id, Department_Id, First_Name, Salary), 
                     T_Arr_Condition(T_Condition('EMPLOYEE_ID', '')));

EMPLOYEE_ID DEPARTMENT_ID FIRST_NAME               SALARY
----------- ------------- -------------------- ----------
        100            90 Steven                    24000
        101            90 Neena                     17000
        102            90 Lex                       17000
        103            60 Alexander                  9000
        104            60 Bruce                      6000
...
...

Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   107 |  1926 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  1926 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

EMPLOYEE_ID DEPARTMENT_ID FIRST_NAME               SALARY
----------- ------------- -------------------- ----------
        100            90 Steven                    24000


Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    18 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    18 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Create Type T_Condition As Object(

Condition_Column_Name Varchar2(128),

Condition_Value Varchar2(4000) -- not used column_value because

);

Create Type T_Arr_Condition Is Table Of T_Condition;

Create Or Replace Function Sf_Generate_Sql (P Dbms_Tf.Table_T, P_Columns Dbms_Tf.Columns_T, P_Where T_Arr_Condition) Return Varchar2 Sql_Macro As

My_Cols Varchar2(4000);

Mycondition Varchar2(4000) ;

Begin

/* be aware of injections! */

For I In 1..P_Columns.Count Loop

My_Cols := My_Cols || P_Columns(I) || ',';

End Loop;

For I In 1..P_Where.Count Loop

If P_Where(I).Condition_Value Is Not Null Then

Mycondition := Mycondition || P_Where(I).Condition_Column_Name || ' = ' || P_Where(I).Condition_Value || ' and ';

End If;

End Loop;

Mycondition := Substr(Mycondition, 1, Length(Mycondition)-4);

If Mycondition Is Not Null Then

Mycondition := ' where '|| Mycondition ;

End If;

My_Cols := Rtrim(My_Cols, ',');

Return 'select '||My_Cols||' from p ' ||Mycondition;

End;

set autotrace on

Select *

From Sf_Generate_Sql(Hr.Employees,

Columns(Employee_Id, Department_Id, First_Name, Salary),

T_Arr_Condition(T_Condition('EMPLOYEE_ID', '')));

EMPLOYEE_ID DEPARTMENT_ID FIRST_NAME SALARY

----------- ------------- -------------------- ----------

100 90 Steven 24000

101 90 Neena 17000

102 90 Lex 17000

103 60 Alexander 9000

104 60 Bruce 6000

...

Execution Plan

----------------------------------------------------------

Plan hash value: 1445457117

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 107 | 1926 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1926 | 3 (0)| 00:00:01 |

-------------------------------------------------------------------------------

EMPLOYEE_ID DEPARTMENT_ID FIRST_NAME SALARY

----------- ------------- -------------------- ----------

100 90 Steven 24000

Execution Plan

----------------------------------------------------------

Plan hash value: 1833546154

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 18 | 1 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 18 | 1 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Viola! if I send null as the value of employee_id, just “select * from hr.employees” statement is run and a full table scan is done but when I send 100 as employee_id value “where employee_id = 100” condition is added.

PS: I am aware that my conditions are just an equality conditions and no AND/OR operator selection or grouping conditions (parenthesises for related OR conditions) etc etc etc. this is just an example after all. I will write a proper version of it when I decided to use it.

set autotrace on;

Select * 
From Sf_Generate_Sql(Hr.Employees, 
                     Columns(Employee_Id, Department_Id, First_Name, Salary), 
                     T_Arr_Condition(T_Condition('EMPLOYEE_ID', '100'),
                                     T_Condition('DEPARTMENT_ID', '90'),
                                     T_Condition('SALARY', '')));

EMPLOYEE_ID DEPARTMENT_ID FIRST_NAME               SALARY
----------- ------------- -------------------- ----------
        100            90 Steven                    24000


Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    18 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    18 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPLOYEES"."DEPARTMENT_ID"=90)
   2 - access("EMPLOYEES"."EMPLOYEE_ID"=100)

set autotrace on;

Select *

From Sf_Generate_Sql(Hr.Employees,

Columns(Employee_Id, Department_Id, First_Name, Salary),

T_Arr_Condition(T_Condition('EMPLOYEE_ID', '100'),

T_Condition('DEPARTMENT_ID', '90'),

T_Condition('SALARY', '')));

EMPLOYEE_ID DEPARTMENT_ID FIRST_NAME SALARY

----------- ------------- -------------------- ----------

100 90 Steven 24000

Execution Plan

----------------------------------------------------------

Plan hash value: 1833546154

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 18 | 1 (0)| 00:00:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 18 | 1 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("EMPLOYEES"."DEPARTMENT_ID"=90)

2 - access("EMPLOYEES"."EMPLOYEE_ID"=100)

as you can see, I send 90 as department_id value and it is in my conditions but value of salary is null and it is not in my conditions. so did we get rid of those conditions? well not exactly.

event if it seems working (and it is working) I did not used any “bind variable” in my examples. those parameters are constant values and so the query. this is the actual running code for my last example:

select “EMPLOYEE_ID”,”DEPARTMENT_ID”,”FIRST_NAME”,”SALARY” from p where EMPLOYEE_ID = 100 and DEPARTMENT_ID = 90

so no bind variables. Let’s use one:

var column1 varchar2(128);
var value1 varchar2(1000);

exec :column1 := 'EMPLOYEE_ID';
exec :value1 := '100';

select *
from Sf_Generate_Sql(hr.employees,
                     columns(employee_id, department_ID, first_name, salary),
                     T_Arr_Condition(T_Condition(:column1, :value1)));
             
from Sf_Generate_Sql(hr.employees,
     *
ERROR at line 2:
ORA-62565: The SQL Macro method failed with error(s).
ORA-06531: Reference to uninitialized collection
ORA-06512: at "MUSTAFA.Sf_Generate_Sql", line 10
ORA-06512: at line 6

var column1 varchar2(128);

var value1 varchar2(1000);

exec :column1 := 'EMPLOYEE_ID';

exec :value1 := '100';

select *

from Sf_Generate_Sql(hr.employees,

columns(employee_id, department_ID, first_name, salary),

T_Arr_Condition(T_Condition(:column1, :value1)));

from Sf_Generate_Sql(hr.employees,

ERROR at line 2:

ORA-62565: The SQL Macro method failed with error(s).

ORA-06531: Reference to uninitialized collection

ORA-06512: at "MUSTAFA.Sf_Generate_Sql", line 10

ORA-06512: at line 6

unfortunately, when I used bind variable I got an error from line 10 of my Sf_Generate_Sql sql macro and line 10 is where the P_Where collection is read. so somehow SQL does not initiate this collection when I used bind variable and/or it is not passed to sql macro. Even if I tried the plsql version, it is not working:

declare
  x1 varchar2(80) := 'department_id';
  x2 varchar2(80):= '50';
  t_c1 t_condition := t_condition(x1,x2);
  x_count number;
  t_arr_c1 t_arr_condition := t_arr_condition(t_c1);
begin
  select * into x_count from Sf_Generate_Sql(hr.employees, columns(employee_id, department_ID, first_name, salary), t_arr_c1);
end;
/

Error report -
ORA-06550: line 8, column 30:
PL/SQL: ORA-62565: The SQL Macro method failed with error(s). 
ORA-06531: Reference to uninitialized collection
ORA-06512: at "MUSTAFA.SF_GENERATE_SQL", line 10
ORA-06512: at line 6

declare

x1 varchar2(80) := 'department_id';

x2 varchar2(80):= '50';

t_c1 t_condition := t_condition(x1,x2);

x_count number;

t_arr_c1 t_arr_condition := t_arr_condition(t_c1);

begin

select * into x_count from Sf_Generate_Sql(hr.employees, columns(employee_id, department_ID, first_name, salary), t_arr_c1);

end;

Error report -

ORA-06550: line 8, column 30:

PL/SQL: ORA-62565: The SQL Macro method failed with error(s).

ORA-06531: Reference to uninitialized collection

ORA-06512: at "MUSTAFA.SF_GENERATE_SQL", line 10

ORA-06512: at line 6

so I can not use it right now. since this is a 20c feature may be we should wait for 21c for more details and more options.

Edit:

I also wanted to add one more example, Let’s get only specified type of columns from any table:

Create Or Replace Function Sf_Get_Columns (P_Table Dbms_Tf.Table_T, P_Type Number default 1) Return Varchar2 Sql_Macro As
  My_Cols Varchar2(4000);
  Current_col boolean := true;
Begin
  
  For I In 1..P_Table.Column.Count Loop
    if P_Type = 1 and P_Table.Column(i).description.type = DBMS_TF.Type_Number then --number
      Current_col := true;
    elsif P_Type = 2 and P_Table.Column(i).description.type = DBMS_TF.Type_Varchar2 then --varchar2
      Current_col := true;
    elsif P_Type = 3 and P_Table.Column(i).description.type = DBMS_TF.Type_Date then -- date
      Current_col := true;
    elsif P_Type = 4 and P_Table.Column(i).description.type = DBMS_TF.Type_Clob then -- clob
      Current_col := true;
    elsif P_Type = 5 and P_Table.Column(i).description.type = DBMS_TF.Type_Blob then -- blob
      Current_col := true;
    else
      Current_col := false;
    end if;
    
    if Current_col then
      My_Cols := My_Cols || P_Table.Column(i).Description.Name || ',';
    end if;

    Current_col := false;
  End Loop;
  
  My_Cols := Rtrim(My_Cols, ',');

  if My_Cols is null then -- if no columns in specified type
    Return 'select * from dual where 1=2';
  else
    Return 'select '||My_Cols||' from P_Table ';
  end if;
  
End;
/

select * from Sf_Get_Columns(hr.employees, 1);

EMPLOYEE_ID     SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- ---------- -------------- ---------- -------------
        100      24000                                      90
        101      17000                       100            90
        102      17000                       100            90
        103       9000                       102            60
        104       6000                       103            60
...
...

select * from Sf_Get_Columns(hr.employees, 2);

FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         JOB_ID
-------------------- ------------------------- ------------------------- -------------------- ----------
Steven               King                      SKING                     515.123.4567         AD_PRES
Neena                Kochhar                   NKOCHHAR                  515.123.4568         AD_VP
Lex                  De Haan                   LDEHAAN                   515.123.4569         AD_VP
Alexander            Hunold                    AHUNOLD                   590.423.4567         IT_PROG
Bruce                Ernst                     BERNST                    590.423.4568         IT_PROG
...

select * from Sf_Get_Columns(hr.employees, 3);

HIRE_DATE
-------------------
2003-06-17 00:00:00
2005-09-21 00:00:00
2001-01-13 00:00:00
2006-01-03 00:00:00
...
...

Create Or Replace Function Sf_Get_Columns (P_Table Dbms_Tf.Table_T, P_Type Number default 1) Return Varchar2 Sql_Macro As

My_Cols Varchar2(4000);

Current_col boolean := true;

Begin

For I In 1..P_Table.Column.Count Loop

if P_Type = 1 and P_Table.Column(i).description.type = DBMS_TF.Type_Number then --number

Current_col := true;

elsif P_Type = 2 and P_Table.Column(i).description.type = DBMS_TF.Type_Varchar2 then --varchar2

Current_col := true;

elsif P_Type = 3 and P_Table.Column(i).description.type = DBMS_TF.Type_Date then -- date

Current_col := true;

elsif P_Type = 4 and P_Table.Column(i).description.type = DBMS_TF.Type_Clob then -- clob

Current_col := true;

elsif P_Type = 5 and P_Table.Column(i).description.type = DBMS_TF.Type_Blob then -- blob

Current_col := true;

else

Current_col := false;

end if;

if Current_col then

My_Cols := My_Cols || P_Table.Column(i).Description.Name || ',';

end if;

Current_col := false;

End Loop;

My_Cols := Rtrim(My_Cols, ',');

if My_Cols is null then -- if no columns in specified type

Return 'select * from dual where 1=2';

else

Return 'select '||My_Cols||' from P_Table ';

end if;

End;

select * from Sf_Get_Columns(hr.employees, 1);

EMPLOYEE_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID

----------- ---------- -------------- ---------- -------------

100 24000 90

101 17000 100 90

102 17000 100 90

103 9000 102 60

104 6000 103 60

...

select * from Sf_Get_Columns(hr.employees, 2);

FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER JOB_ID

-------------------- ------------------------- ------------------------- -------------------- ----------

Steven King SKING 515.123.4567 AD_PRES

Neena Kochhar NKOCHHAR 515.123.4568 AD_VP

Lex De Haan LDEHAAN 515.123.4569 AD_VP

Alexander Hunold AHUNOLD 590.423.4567 IT_PROG

Bruce Ernst BERNST 590.423.4568 IT_PROG

...

select * from Sf_Get_Columns(hr.employees, 3);

HIRE_DATE

-------------------

2003-06-17 00:00:00

2005-09-21 00:00:00

2001-01-13 00:00:00

2006-01-03 00:00:00

...

I defined 1 as number, 2 as varchar2 etc etc. so I can get only NUMBER columns from a table (any table actually) or only varchar2 columns.

SQL Macro has a great functionality improvement for SQL in my opinion. since 20c is not accessible yet I can not try SCALAR sql macro but I am confident that it will save us to write many same sql fragments for many different sql statements.

wish you healthy days.

Leave a Reply Cancel reply