Skip to content
Keep Learning Keep Living
Keep Learning Keep Living
Keep Learning Keep Living

SQL Macro is finally here!

Mustafa, 2020-10-282020-10-29

Hi,

I was waiting for this for a long time. Finally we can use sql macro in Oracle database. Actually I was excited when I first read about this but after my tests, I stuck at some points. I was hoping to use this as “parameterized views” as in, send a parameter and use it to choose which select statement runs and I am kind of able to do that but not I expected. I have read about the documentation but not much detail. I will mentioned about some “odd” staff that I found about sql macro at the end of this post.

And this could help us about the conditions of: “where customer_name = nvl(:Cust_name, customer_name”. I think many of you know this and suffer it right 🙂 if parameter is null then bring all of them but if it is not null then just bring matched rows.


EDIT: bummer! thanks to Mike Cutz from oracle community, showed me a mistake on my performance compare below. while I was testing, I used constant ‘EMPLOYEES’ value instead of bind variable. so I am changing that part.


So, what is sql macro. Sql macro is an SQL add-on to enhance the sql capability. mostly we use PLSQL when we need to do something complex and can not do it just by sql but this cause “context switch”. you can think this as, there are two engines in Oracle, one is SQL and the other PLSQL. two engines does not do other’s job. if a plsql statement requires to run an SQL then statements is sent to SQL engine and if sql engine finds a plsql (a function in select statement for ex) then it is sent to plsql engine to run. going back and forward between these engines is called “context switch” and context switch is a performance ENEMY! So we always try to do our job in just one engine but sometimes we have to use both. This is a new feature for 20c but I am running those code on 19.6.

SQL Macro will reduce the requirement of PLSQL so there won’t be context switch and we will have more performance. Also, it does help about getting better execution plans for complex queries some how. There are 2 types of SQL Macro, Scalar and Table. default is table and I will demonstrate table SM here.

Table SQL Macro works and looks like pipelined functions but remember, this is run by sql engine not by plsql.

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create table tmp as select * from all_objects;
create index i_tmp1 on tmp(object_name);
create index i_tmp2 on tmp(object_id);
create index i_tmp3 on tmp(data_object_id);
create index i_tmp4 on tmp(created, object_name);
create index i_tmp5 on tmp(owner, created);
 
exec dbms_stats.gather_Table_Stats(user, 'TMP', estimate_percent=>100, cascade => true);
 
create or replace function sf_test(p_objecT_name varchar2) return varchar2 SQL_MACRO as
begin
 
  return 'select * from tmp
          where object_name = sf_test.p_object_name';  
end;
/

I just created a dummy table and created SF_TEST sql macro. sf_test looks like just a regular function with one varchar2 parameter but at the definition we use SQL_MACRO before “as” keyword. and then just return a string which has a select statement. here is usage:

Oracle PL/SQL
1
2
3
4
5
select * from sf_Test('EMPLOYEES');
 
OWNER  OBJECT_NAME                    SUBOBJECT_NAME    OBJECT_ID...
HR     EMPLOYEES                                        75462...
OE     EMPLOYEES                                        75518...

as you see our sql macro worked as a pipelined function and returned object information (TMP table) with named EMPLOYEES. basically it is just running the select statement in the sql macro and this operation is done in sql engine (well at least mostly…).

let’s make another example:

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
create or replace function sf_fetch_n_rows (p_number_of_rows number) return varchar2 sql_macro as
begin
  return 'select *
          from tmp
          fetch first sf_fetch_n_rows.p_number_of_rows rows only';  
end;
/
 
select * from sf_fetch_n_rows(2);
 
OWNER  OBJECT_NAME                    SUBOBJECT_NAME    OBJECT_ID...
SYS    TS$                                              16...
SYS    ICOL$                                            20...
 
select * from sf_fetch_n_rows(4);
 
OWNER  OBJECT_NAME                    SUBOBJECT_NAME    OBJECT_ID...
SYS    TS$                                              16...
SYS    ICOL$                                            20...
SYS    C_FILE#_BLOCK#                                   8...
SYS    I_OBJ2                                           37...

cool right? I can parameterized the number of rows to return from an sql statement.

Let’s take it further:

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create or replace function sf_fetch_n_rows_from_any_table (p_number_of_rows number, p_table dbms_tf.TABLE_T) return varchar2 sql_macro as
begin
  return 'select *
          from sf_fetch_n_rows_from_any_table.p_table
          fetch first sf_fetch_n_rows_from_any_table.p_number_of_rows rows only';  
end;
/
 
select * from sf_fetch_n_rows_from_any_table(4, hr.employees);
 
EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE           JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ------------------- ---------- ---------- -------------- ---------- -------------
        100 Steven               King                      SKING                     515.123.4567         2003-06-17 00:00:00 AD_PRES         24000                                      90
        101 Neena                Kochhar                   NKOCHHAR                  515.123.4568         2005-09-21 00:00:00 AD_VP           17000                       100            90
        102 Lex                  De Haan                   LDEHAAN                   515.123.4569         2001-01-13 00:00:00 AD_VP           17000                       100            90
        103 Alexander            Hunold                    AHUNOLD                   590.423.4567         2006-01-03 00:00:00 IT_PROG          9000                       102            60

this is really cool! we can pass a “table” as parameter (which is come with polymorphic table functions actually) to our sql macro and run our query based on this table. in my example I passed 4 (number of rows) and “hr.employees” table. please be careful, hr.employees is not a varchar2 variable! this is not a string concatenation operation. Now we can get any rows from any table:

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
select * from sf_fetch_n_rows_from_any_table(1, DBA_CONNECTION_TESTS);
 
P CONNECTION_TEST SQL_CONNECTION_TEST    SERVICE_NAME E
- --------------- ----------------------------------- -
Y SQL_TEST        SELECT 1 FROM DUAL                  Y
 
select * from sf_fetch_n_rows_from_any_table(3, hr.departments);
 
DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           10 Administration                        200        1700
           20 Marketing                             201        1800
           30 Purchasing                            114        1700

So, let’s get back to performance enhancement. Actually I am not sure about the exact result but I run a few simple test and it showed, execution plans are more accurate. here is an example:

Oracle PL/SQL
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
set autotrace traceonly
 
var p_object_id number;
var p_object_name varchar2(128);
var p_object_type varchar2(128);
var p_owner varchar2(128);
 
exec :p_object_name := 'EMPLOYEES';
 
    select *
    from tmp
    where object_id = nvl(:p_object_id,object_id)
      and object_name = nvl(:p_object_name,object_name)
      and object_type = nvl(:p_object_type,object_type)
      and owner = nvl(:p_owner,owner);
 
2 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2707160492
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |     2 |   962 |   377   (2)| 00:00:01 |
|   1 |  VIEW                                  | VW_ORE_A06AD640 |     2 |   962 |   377   (2)| 00:00:01 |
|   2 |   UNION-ALL                            |                 |       |       |            |          |
|*  3 |    FILTER                              |                 |       |       |            |          |
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED| TMP             |     1 |   133 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN                  | I_TMP2          |     1 |       |     1   (0)| 00:00:01 |
|*  6 |    FILTER                              |                 |       |       |            |          |
|*  7 |     TABLE ACCESS FULL                  | TMP             |     1 |   133 |   375   (2)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(:P_OBJECT_ID IS NOT NULL)
   4 - filter("OBJECT_NAME"=NVL(:P_OBJECT_NAME,"OBJECT_NAME") AND
              "OBJECT_TYPE"=NVL(:P_OBJECT_TYPE,"OBJECT_TYPE") AND "OWNER"=NVL(:P_OWNER,"OWNER"))
   5 - access("OBJECT_ID"=:P_OBJECT_ID)
   6 - filter(:P_OBJECT_ID IS NULL)
   7 - filter("OBJECT_NAME"=NVL(:P_OBJECT_NAME,"OBJECT_NAME") AND
              "OBJECT_TYPE"=NVL(:P_OBJECT_TYPE,"OBJECT_TYPE") AND "OWNER"=NVL(:P_OWNER,"OWNER"))

now, I make my first query a little bit complex with using some NVL functions and bind variables. now I use 4 bind variables (owner, object_name, object_type, object_id) but only OBJECT_NAME has a value and others are null. also please remember I created and index on OBJECT_NAME on TMP table. so in this query I am looking for all objects with name EMPLOYEES but since Oracle needs to check all values if they are null or not, execution plan becomes more complex and cost is higher. the equivalent version of this query (with this bind variable values) is:

Select * from tmp where object_name = ‘EMPLOYEES’;

and this is a very simple query which can use the index on TMP table.

EDIT: I deleted this part and rewriting it please see my previous edit at top.

I will discuss about reading variable values in sql macro after that but as a sneak peek, you can not read any variable value in sql macro except NUMBER types. so to eliminate the problem above (column = nvl(paramater, column)) we can at least do something by adding more number variables:

Oracle PL/SQL
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
set autotrace traceonly;
 
set serveroutput on;
 
var table_name varchar2(128);
exec :table_name := 'EMPLOYEES';
 
create or replace function sf_test(p_owner varchar2, p_owner_null number,
                                   p_object_name varchar2,  p_object_name_null number,
                                   p_object_id number,  p_object_id_null number,
                                   p_object_type varchar2,  p_object_type_null number) return varchar2 sql_macro as
  v_sql varchar2(4000) :=  'select * from tmp ';
  v_conditions varchar2(4000);
begin
  if p_owner_null = 0 then
    v_conditions := v_conditions || ' owner = sf_test.p_owner and ';
  end if;
 
  if p_object_name_null = 0 then
    v_conditions := v_conditions || ' object_name = sf_test.p_object_name and ';
  end if;
 
  if p_object_id_null = 0 then
    v_conditions := v_conditions || ' object_id = sf_test.p_object_id and ';
  end if;
 
  if p_object_type_null = 0 then
    v_conditions := v_conditions || ' object_type = sf_test.p_object_type and ';
  end if;
  
  if v_conditions is not null then
    v_conditions := ' where ' || v_conditions ;
  end if;
  
  v_conditions := substr(v_conditions, 1, length(v_conditions)-4); --just remove last " and "
  
  --dbms_output.put_line(v_Sql || v_conditions);
    
  return v_Sql ||v_conditions ;  
end;
/
 
select * from sf_Test(null,1, :table_name,0, null,1,null,1);
 
Execution Plan
----------------------------------------------------------
Plan hash value: 194862134
 
----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |     1 |   133 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TMP    |     1 |   133 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | I_TMP1 |     1 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("OBJECT_NAME"=:TABLE_NAME)

we can shape our sql in sql macro. as I said since I can only read NUMBER variables, I added parameters that indicates whether the real parameter is null or not and based on this information I constracted my sql statements.


What is “odd” about this?

when I see the first examples, I thought to create condition list using the parameter values. if a parameter is not null then add “column = p_parameter1” to query but something is different in this environment. even if we are writing a “function” and can use plsql commands like “if then else” or for loop, we can not read parameter values (even more oddly) except NUMBERS.

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
set autotrace off;
set serveroutput on;
 
create or replace function sf_test_output(p_param number) return varchar2 sql_macro as
    begin
      dbms_output.put_line('parameter p_param is ' || sf_test_output.p_param);
      return 'select sf_test_output.p_param from dual';
    end;
    /
 
 
select * from sf_test_output(10);
 
   P_PARAM
----------
        10
 
1 row selected.
 
parameter p_param is 10

everything is normal here, I am showing the value using dbms_output and also my select is fetching parameter value from dual and both okay. query returned 1 row with value 10 and output is showing correct value.

Let’s do it with a varchar2:

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create or replace function sf_test_output(p_param varchar2) return varchar2 sql_macro as
        begin
          dbms_output.put_line('parameter p_param is ' || sf_test_output.p_param);
          return 'select sf_test_output.p_param from dual';
        end;
        /
 
Function created.
 
 
select * from sf_test_output('ABC');
 
P_P
---
ABC
 
1 row selected.
 
parameter p_param is

as you see, when parameter type is varchar2, then I can not read it inside the sql macro! my output is null (parameter p_param is) but query returned the value of parameter. same thing happens for date type too. So I can not use “if p_param = ‘ABC’ then” condition because p_param is null inside the sql macro but I can use “if p_param = 1 then” if p_param is a number.

I am running these codes on version 19.6 but also run them livesql.oracle.com which is 19.8 and same result.

I don’t know why it is acting like this maybe a bug maybe a feature 😀 I told that this is run by sql not plsql but I think this is not entirely true. some plsql engine is involved here but parameter values are not passed to plsql engine except numbers. of course this is just a guess I am not sure about it but I would like to use parameter values inside the sql macro.

wish you all healthy, corona free days.

19c Development Performance SQL / PLSQL 19c new feature20c new featuredynamic table queryparameterized viewsql macrosql performance

Post navigation

Previous post
Next post

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage

Recent Comments

  • Mustafa on How to call HTTPS Url Without SSL Wallet in 19c
  • Накрутка авито on How to call HTTPS Url Without SSL Wallet in 19c
  • Mustafa on Cloud Base Database Service
  • Raja on Cloud Base Database Service
  • Mustafa on Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Categories

  • 11g
  • 12c
  • 18c
  • 19c
  • 21c
  • 23ai
  • Administration
  • Cloud
  • Compression
  • Development
  • Materialized View
  • Multi-tenant
  • Performance
  • Security
  • SQL / PLSQL
  • Uncategorized
  • Undocumented
  • Useful Scripts

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed
RSS Error: A feed could not be found at `http://www.mywebsite.com/feed/`; the status code is `200` and content-type is `text/html; charset=UTF-8`
©2025 Keep Learning Keep Living | WordPress Theme by SuperbThemes