Hi,
Since I am working on “SQL Macro”, I am keep testing things. so please see my previous post: http://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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
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 |
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?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 |
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 | --------------------------------------------------------------------------------------------- |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
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) |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
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.