Frequency-Based Ranges – Generalized with SQL Macro

In the previous post I used MATCH_RECOGNIZE to convert a list of concrete event times to frequency-based ranges.
The same technique can be applied with different tables, so I wrapped the query in a Table SQL Macro.

The parameters are the table, the “type” column, the “time” column, and (optionally) the period used for filtering.
I assumed the frequencies are always in minutes. The macro can be further generalized to support different time resolutions.

Here is the code:

 create or replace function freq_view ( i_table in dbms_tf.table_t, i_columns in dbms_tf.columns_t, i_from_time in date default date '1970-01-01', i_to_time in date default date '9999-12-31' ) return varchar2 sql_macro as begin return 'select event_type ' || i_columns(1) || ', to_char(from_time,''yyyy-mm-dd'') "DATE", to_char(from_time,''hh24:mi'') || nvl2(to_time, '' - '' || to_char(to_time,''hh24:mi'') || '' every '' || minutes || '' minutes ('' || number_of_events || '' events)'', null) event_times from ( select ' || i_columns(1) || ' as event_type, trunc(' || i_columns(2) || ',''mi'') as event_time from i_table where ' || i_columns(2) || ' between i_from_time and i_to_time ) match_recognize ( partition by event_type order by event_time measures X.event_time from_time, last(Y.event_time) to_time, round((first(Y.event_time)-X.event_time)*24*60) minutes, count(*) number_of_events pattern (X Y{2,} | X) define Y as event_time-prev(event_time) = first(Y.event_time)-X.event_time ) order by event_type, from_time'; end freq_view; /

Let’s use it for the same data set used in the original post:

 SQL> break on event_type skip 1 dup SQL> select * from freq_view(events,columns(event_type,event_time),timestamp'2022-07-29 08:00:00'); EVENT_ DATE EVENT_TIMES ------ -------------- ---------------------------------------------- A 2022-07-29 08:10 - 08:50 every 10 minutes (5 events) A 2022-07-29 08:58 - 09:22 every 8 minutes (4 events) A 2022-07-29 09:33 A 2022-07-29 09:42 - 10:52 every 10 minutes (8 events) A 2022-07-29 10:59 - 11:11 every 4 minutes (4 events) A 2022-07-29 11:21 - 12:01 every 10 minutes (5 events) B 2022-07-29 08:15 - 09:55 every 20 minutes (6 events) B 2022-07-29 10:10 B 2022-07-29 10:25 - 12:05 every 25 minutes (5 events) C 2022-07-29 08:02 - 08:32 every 2 minutes (16 events) C 2022-07-29 08:49 - 09:05 every 2 minutes (9 events) C 2022-07-29 09:08 - 09:20 every 4 minutes (4 events) C 2022-07-29 09:22 - 10:16 every 2 minutes (28 events) C 2022-07-29 11:46 - 12:20 every 2 minutes (18 events) 14 rows selected.

And now let’s use it for something else – the DBMS_SCHEDULER executions from the last 3 hours:

 SQL> select * from freq_view(user_scheduler_job_run_details,columns(job_name,log_date),sysdate-3/24); JOB_NAME DATE EVENT_TIMES ------------ --------------- ------------------------------------------------ JOB_AAA 2022-07-30 08:45 - 10:45 every 60 minutes (3 events) JOB_BBB 2022-07-30 08:10 - 10:10 every 60 minutes (3 events) JOB_CCC 2022-07-30 08:07 - 11:02 every 5 minutes (36 events) JOB_DDD 2022-07-30 10:00 JOB_EEE 2022-07-30 08:06 - 11:05 every 1 minutes (180 events)

Pattern Matching and Event Frequencies

I have a table that contains events of various types:

 create table events ( event_id integer not null, event_type varchar2(2) not null, event_time date not null, -- some other columns with the event details );

The events may arrive sporadically, but many times multiple consecutive events of the same type arrive in a fixed frequency (e.g., “events of type A happen every 10 minutes between 8:10am and 8:50am”).

I want to list all the event times in a specific period for every event type.

This is a simple query, but there are many results, so it’s not very easy to read them:

 SQL> break on event_type skip 1 dup SQL> select event_type, 2 event_time 3 from events 4 where event_time > timestamp'2022-07-29 08:00:00' 5 order by event_type, 6 event_time; EVENT TYPE EVENT_TIME ----- ------------------- A 2022-07-29 08:10:00 A 2022-07-29 08:20:00 A 2022-07-29 08:30:00 A 2022-07-29 08:40:00 A 2022-07-29 08:50:00 A 2022-07-29 08:58:00 A 2022-07-29 09:06:00 A 2022-07-29 09:14:00 A 2022-07-29 09:22:00 A 2022-07-29 09:33:00 A 2022-07-29 09:42:00 A 2022-07-29 09:52:00 A 2022-07-29 10:02:00 A 2022-07-29 10:12:00 A 2022-07-29 10:22:00 A 2022-07-29 10:32:00 A 2022-07-29 10:42:00 A 2022-07-29 10:52:00 A 2022-07-29 10:59:00 A 2022-07-29 11:03:00 A 2022-07-29 11:07:00 A 2022-07-29 11:11:00 A 2022-07-29 11:21:00 A 2022-07-29 11:31:00 A 2022-07-29 11:41:00 A 2022-07-29 11:51:00 A 2022-07-29 12:01:00 B 2022-07-29 08:15:00 B 2022-07-29 08:35:00 B 2022-07-29 08:55:00 B 2022-07-29 09:15:00 B 2022-07-29 09:35:00 B 2022-07-29 09:55:00 B 2022-07-29 10:10:00 B 2022-07-29 10:25:00 B 2022-07-29 10:50:00 B 2022-07-29 11:15:00 B 2022-07-29 11:40:00 B 2022-07-29 12:05:00 C 2022-07-29 08:02:00 C 2022-07-29 08:04:00 C 2022-07-29 08:06:00 C 2022-07-29 08:08:00 C 2022-07-29 08:10:00 C 2022-07-29 08:12:00 C 2022-07-29 08:14:00 C 2022-07-29 08:16:00 C 2022-07-29 08:18:00 C 2022-07-29 08:20:00 C 2022-07-29 08:22:00 C 2022-07-29 08:24:00 C 2022-07-29 08:26:00 C 2022-07-29 08:28:00 C 2022-07-29 08:30:00 C 2022-07-29 08:32:00 C 2022-07-29 08:49:00 C 2022-07-29 08:51:00 ... 114 rows selected.

Because this list contains batches of events with a fixed frequency, we can change the query to get a nicer output – with a single line for every batch.
Identifying these batches is easy with the MATCH_RECOGNIZE clause.

Let’s see first the output of the query:

 EVENT TYPE DATE EVENT_TIMES ----- ------------ ------------------------------------------- A 2022-07-29 08:10 - 08:50 every 10 minutes (5 events) A 2022-07-29 08:58 - 09:22 every 8 minutes (4 events) A 2022-07-29 09:33 A 2022-07-29 09:42 - 10:52 every 10 minutes (8 events) A 2022-07-29 10:59 - 11:11 every 4 minutes (4 events) A 2022-07-29 11:21 - 12:01 every 10 minutes (5 events) B 2022-07-29 08:15 - 09:55 every 20 minutes (6 events) B 2022-07-29 10:10 B 2022-07-29 10:25 - 12:05 every 25 minutes (5 events) C 2022-07-29 08:02 - 08:32 every 2 minutes (16 events) C 2022-07-29 08:49 - 09:05 every 2 minutes (9 events) C 2022-07-29 09:08 - 09:20 every 4 minutes (4 events) C 2022-07-29 09:22 - 10:16 every 2 minutes (28 events) C 2022-07-29 11:46 - 12:20 every 2 minutes (18 events) 14 rows selected.

Much nicer, isn’t it? 🙂

Here is the query:

 SQL> select event_type, 2 to_char(from_time,'yyyy-mm-dd') "DATE", 3 to_char(from_time,'hh24:mi') || 4 nvl2(to_time, 5 ' - ' || to_char(to_time,'hh24:mi') 6 || ' every ' || minutes || ' minutes (' 7 || number_of_events || ' events)', 8 null) event_times 9 from ( 10 select event_type,event_time 11 from events 12 where event_time > timestamp'2022-07-29 08:00:00' 13 ) match_recognize ( 14 partition by event_type 15 order by event_time 16 measures X.event_time from_time, 17 last(Y.event_time) to_time, 18 (first(Y.event_time)-X.event_time)*24*60 minutes, 19 count(*) number_of_events 20 pattern (X Y{2,} | X) 21 define 22 Y as event_time-prev(event_time) = first(Y.event_time)-X.event_time 23 ) 24 order by event_type, from_time;

The original query [lines 10-12] is the input to the MATCH_RECOGNIZE clause [lines 13-23].

We partition the input record-set by EVENT_TYPE [line 14], as we want to get a separate list for each type.

MATCH_RECOGNIZE is used for finding consecutive rows that match some pattern. “Consecutive” means we have to define the order of the input record-set (in each partition). In this case, we order the events of each type by EVENT_TIME [line 15].

Now, let’s define the pattern [lines 20-22]. We’re looking for at least 3 consecutive events – an X record followed by two or more Y records [“X Y{2,}”].
Any event is potentially an X record, so I didn’t define it explicitly.
Y is explicitly defined [line 22] as a record that its time difference from the previous record [event_time-prev(event_time)] is the same as the time difference between the first event [X] and the second event [the first Y] in the current batch [first(Y.event_time)-X.event_time].
If we don’t find such a batch of events [X followed by two or more Ys], we have a single event to return – X. This is expressed by the pattern “X Y{2,} | X” [line 20].

For every match of this pattern, 4 expressions are calculated [lines 16-19]:
FROM_TIME: the time of the first event in the match [line 16]
TO_TIME: the time of the last event in the match [line 17]
MINUTES: the time difference between every two consecutive events [line 18]
NUMBER_OF_EVENTS: the total number of events in the match [line 19]

Finally, we display the results returned by the MATCH_RECOGNIZE operator, using the expressions calculated in the MEASURES clause [lines 1-8].

The same technique can be applied for different tables. See the next post for a generalized solution, using a Table SQL Macro.

FORALL – INDICES OF vs. Lower and Upper Bounds

When using the FORALL statement, I like to feed the index variable with the INDICES OF clause, rather than using the lower_bound .. upper_bound option.
INDICES OF is perfect when the corresponding collection may be sparse, but I prefer using it also when the collection is dense. In my opinion, this is a more elegant structure, self documented, and the use of one pattern throughout the system makes the code more consistent and easier to maintain.

But is this only a question of personal preference?
Is the difference between these two options only syntactic?
In other words, is this:

 SQL> truncate table t; Table truncated. SQL> declare 2 v_collection sys.odcinumberlist; 3 begin 4 insert into t (x,y) 5 select rownum,rownum from dual connect by level <= 10; 6 7 v_collection := sys.odcinumberlist(1,7,43); 8 forall i in 1 .. v_collection.count 9 update t 10 set y = 0 11 where x = v_collection(i); 12 dbms_output.put_line(sql%rowcount || ' rows updated'); 13 end; 14 / 2 rows updated PL/SQL procedure successfully completed.

completely identical to this:

 SQL> truncate table t; Table truncated. SQL> declare 2 v_collection sys.odcinumberlist; 3 begin 4 insert into t (x,y) 5 select rownum,rownum from dual connect by level <= 10; 6 7 v_collection := sys.odcinumberlist(1,7,43); 8 forall i in indices of v_collection 9 update t 10 set y = 0 11 where x = v_collection(i); 12 dbms_output.put_line(sql%rowcount || ' rows updated'); 13 end; 14 / 2 rows updated PL/SQL procedure successfully completed.

?

In most cases the answer is yes, but a few days ago I found a case where the behavior is different between the two options.

I was wondering what happens exactly when the collection is empty.
This is not a hypothetical question. Many times we populate a collection from a query, and then use this collection in a FORALL statement to perform some DML statement. It is legit that the query will return no records, and therefore the collection will be empty.

I did some tracing and found out that in the following example, when using INDICES OF, there is a context switch from the PL/SQL engine to the SQL engine – the UPDATE statement is executed, and since the collection is empty no binding happens and no rows are affeceted.

 v_collection := sys.odcinumberlist(); forall i in indices of v_collection update t set y = 0 where x = v_collection(i); 
 ===================== PARSING IN CURSOR #1808232998640 len=33 dep=1 uid=154 oct=6 lid=154 tim=153168435007 hv=2619660526 ad='7ffeeeaf44d0' sqlid='7n7dshqf29q7f' UPDATE T SET Y = 0 WHERE X = :B1 END OF STMT PARSE #1808232998640:c=0,e=126,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=153168435007 ===================== ... EXEC #1808232998640:c=0,e=2893,p=0,cr=7,cu=0,mis=1,r=0,dep=1,og=1,plh=931696821,tim=153168437995 CLOSE #1808232998640:c=0,e=1,dep=1,type=3,tim=153168438053

While in the following example, when using the lower_bound..upper_bound syntax, there is no context switch. The PL/SQL engine does an optimization here – it understands that going from 1 to 0 yields no items, and it doesn’t bother calling the SQL engine.

 v_collection := sys.odcinumberlist(); forall i in 1 .. v_collection.count update t set y = 0 where x = v_collection(i);

The outcome is the same – nothing had to be updated anyway. From the performance perspective, the lower_bound..upper_bound option spared a context switch, so it’s probably better (I didn’t measure it). I assume it’s negligible in most cases (unless you make lots of calls with empty collections).

But there may be also consequences to this optimization.
Here is one such consequence I was thinking about.
Look at this example, and see if you find any problem with it:

 truncate table t; declare v_collection sys.odcinumberlist; begin insert into t (x,y) select rownum,rownum from dual connect by level <= 10; v_collection := sys.odcinumberlist(); forall i in indices of v_collection update t set y = 0 where x = v_collection(i); dbms_output.put_line(sql%rowcount || ' rows updated'); end; / truncate table t; declare v_collection sys.odcinumberlist; begin insert into t (x,y) select rownum,rownum from dual connect by level <= 10; v_collection := sys.odcinumberlist(); forall i in 1 .. v_collection.count update t set y = 0 where x = v_collection(i); dbms_output.put_line(sql%rowcount || ' rows updated'); end; /

What do you expect to see as the output of each case?

Here is the output when using INDICES OF:

 SQL> declare 2 v_collection sys.odcinumberlist; 3 begin 4 insert into t (x,y) 5 select rownum,rownum from dual connect by level <= 10; 6 7 v_collection := sys.odcinumberlist(); 8 forall i in indices of v_collection 9 update t 10 set y = 0 11 where x = v_collection(i); 12 dbms_output.put_line(sql%rowcount || ' rows updated'); 13 end; 14 / 0 rows updated PL/SQL procedure successfully completed.

As expected, 0 rows updated.

Now, what happens with the second anonymous block?

 SQL> declare 2 v_collection sys.odcinumberlist; 3 begin 4 insert into t (x,y) 5 select rownum,rownum from dual connect by level <= 10; 6 7 v_collection := sys.odcinumberlist(); 8 forall i in 1 .. v_collection.count 9 update t 10 set y = 0 11 where x = v_collection(i); 12 dbms_output.put_line(sql%rowcount || ' rows updated'); 13 end; 14 / 10 rows updated PL/SQL procedure successfully completed.

10 rows updated!
SQL%ROWCOUNT returns the number of affected rows from the latest executed SQL statement. The FORALL statement did not execute the UPDATE statement, so the latest SQL statement that was actually executed is the INSERT statement.
This can lead to confusing situations and potential bugs.

So now I have one more reason to prefer INDICES OF 🙂

Conditional Outer Joins

In the previous post we saw how to convert an outer join with no join condition from ANSI syntax to Oracle syntax.
In this post we’ll do the same for outer joins with conditional join conditions.

In the following query the join includes a filter condition on t2:

 select * from t1 left outer join t2 on (t2.t1_id = t1.id and t2.x = :x);

Converting this query to the Oracle syntax is straightforward:

 select * from t1, t2 where t2.t1_id(+) = t1.id and t2.x(+) = :x;

In the following query the join includes a filter condition on t1:

 select * from t1 left outer join t2 on (t2.t1_id = t1.id and t1.a = :a);

Converting this query to the Oracle syntax is a bit more tricky. Here is one option how to do it:

 select * from t1, t2 where t2.t1_id(+) = decode(t1.a, :a, t1.id);

Outer Join with no Join Condition

I prefer the “Oracle syntax” for outer joins over the ANSI syntax.
I like the conciseness of the Oracle syntax, and I’ve been using it for the last 30 years or so.
I totally understand people that prefer the ANSI syntax.
I agree that the ANSI syntax is richer and more complete (although Oracle 12c reduced the gaps by adding more capabilities to the Oracle syntax).
I get it that for many people the ANSI syntax is easier to read and write.
I believe that both styles are legit. But I think it’s important to choose only one of them and make this choice part of your coding standards – whether it is for a specific project or company-wide or anything in between; in the level in which coding standards are defined in your organization.

By the way, even when using the ASNI syntax, Oracle converts it internally to its proprietary syntax

So for me the standard is using the Oracle syntax, and in 99% of the queries it’s trivial. But sometimes there are queries that are more challenging. This post is about one such case.

I want to get all the columns from a specific record in T1. In addition, I want to get the column X of a specific record from T2, if such a record exists.
This is the query using the ANSI syntax:

 select t1.*, t2.x from t1 left join t2 on t2.id = :b2 where t1.id = :b1;

How can we write it using the Oracle syntax?
One of the limitations of the Oracle syntax for left/right outer join is that there must be at least one join condition. If there are only filter conditions, the outer join operator (+) is silently ignored and the query becomes de facto an inner join. So writing the query like this

 select t1.*, t2.x from t1, t2 where t1.id = :b1 and t2.id(+) = :b2;

will not give us the required results. This is not an outer join. We can see from the execution plan that the join operation is NESTED LOOPS and not NESTED LOOPS OUTER.

 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 113 | 2 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 113 | 2 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 26 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | T2_PK | 1 | | 1 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 87 | 1 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | T1_PK | 1 | | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T2"."ID"=TO_NUMBER(:B2)) 5 - access("T1"."ID"=TO_NUMBER(:B1))

Since we need only one column from T2, we can simply use a scalar subquery:

 select t1.*,  (select t2.x from t2 where t2.id = :b2) x from t1 where t1.id = :b1;

But what if we want to get all the columns from T2? I wouldn’t use a scalar subquery for every column.
This is the query using the ANSI syntax:

 select t1.*, t2.* from t1 left join t2 on t2.id = :b2 where t1.id = :b1;

We can get an idea how to write it using the Oracle syntax, by looking at the execution plan of the previous query:

 ORA$BASE> @x 2 select t1.*, t2.* 3 from t1 4 left join t2 on t2.id = :b2 5 where t1.id = :b1; Explained. ORA$BASE> @xplan PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 1875989973 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 139 | 2 (0)| 00:00:01 | | 1 | NESTED LOOPS OUTER | | 1 | 139 | 2 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 87 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | T1_PK | 1 | | 1 (0)| 00:00:01 | | 4 | VIEW | VW_LAT_C83A7ED5 | 1 | 52 | 1 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 52 | 1 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | T2_PK | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T1"."ID"=TO_NUMBER(:B1)) 6 - access("T2"."ID"=TO_NUMBER(:B2)) 19 rows selected.

The execution plan shows that Oracle does a NESTED LOOPS OUTER Join between T1 and a LATERAL Inline View (note the VIEW operation with the VW_LAT… name) that contains the subquery on T2.
We can do the same. We put the subquery on T2 inside an inline view, and then apply the outer join operator (+) on the inline view. In order to do it we have to declare the inline view as LATERAL (although we don’t refer to any column of T1 in the inline view), because (+) is not allowed on non-lateral inline views.

 select t1.*, t2.* from t1, lateral (select t2.* from t2 where t2.id = :b2)(+) t2 where t1.id = :b1;

Anther option is to trick the optimizer, and make it believe the filter condition on T2 is actually a join condition. We can do it by including some reference to T1 in the filtering expression that doesn’t change the expression value. Here are two examples:

 select t1.*, t2.* from t1, t2 where t1.id = :b1 and t2.id(+) = nvl2(t1.id,:b2,:b2) ; 
 select t1.*, t2.* from t1, t2 where t1.id = :b1 and t2.id(+) = t1.id-t1.id + :b2 ;

ANY_VALUE and FIRST/LAST (KEEP)

The FIRST and LAST (a.k.a. “KEEP”) aggregate functions are very useful when you want to order a row set by one column and return the value of another column from the first or last row in the ordered set.
I wrote about these functions in the past; for example here and here.

To make sure the result of the FIRST (LAST) function is deterministic, we have to define a tie-breaker for the case that multiple rows have the same first (last) value. The tie-breaker is an aggregate function that is applied on the column we want to return.

For example, the following query returns for each department the FIRST_NAME of the employee with the lowest SALARY. If there are multiple employees with the lowest salary in the same department, the query returns the “minimum” first_name – so here the MIN function is the tie-breaker.
In this example the lowest salary in department 90 is 17000, and both Neena and Lex have this salary. We use MIN(FIRST_NAME), so the result is “Lex”.

Many times we don’t really need a tie-breaker, because we know that there is a single first/last row. For example, if we use a unique expression in the ORDER BY clause of the function. And sometimes we simply don’t care which record is returned in case of a tie. But since the syntax requires a tie-breaker, we have to use some “random” function, like MIN or MAX.

The ANY_VALUE function (that was added in Oracle 19c) is perfect, in my opinion, for this case. It may improve performance, but – more importantly – it makes the code clearer, by better reflecting our intention.
Assuming that in the previous example we don’t care which one of the employees with the lowest salary is returned, we can rewrite it like this:

 select department_id, any_value(first_name) keep(dense_rank FIRST order by salary) from employees group by department_id;

DIY ANY_VALUE

I really like the ANY_VALUE aggregate function. But since it was added in Oracle 19c, I can’t use it in my Oracle XE database, as currently the latest version of XE is 18c.
So I decided to implement it as a user-defined function using the Oracle Data Cartridges Interface.

Note that this implementation is for VARCHAR2. If the function is used on other data types, the regular rules for implicit conversion apply.

 SQL> create type any_value_string_t as object 2 ( 3 v_value varchar2(4000), 4 static function odciaggregateinitialize(sctx in out any_value_string_t) return number, 5 member function odciaggregateiterate 6 ( 7 self in out any_value_string_t, 8 value in varchar2 9 ) return number, 10 member function odciaggregatemerge 11 ( 12 self in out any_value_string_t, 13 ctx2 in any_value_string_t 14 ) return number, 15 member function odciaggregateterminate 16 ( 17 self in any_value_string_t, 18 returnvalue out varchar2, 19 flags in number 20 ) return number 21 ); 22 / Type created. SQL> create type body any_value_string_t as 2 3 static function odciaggregateinitialize(sctx in out any_value_string_t) return number is 4 begin 5 sctx := any_value_string_t(null); 6 return odciconst.success; 7 end; 8 9 member function odciaggregateiterate 10 ( 11 self in out any_value_string_t, 12 value in varchar2 13 ) return number is 14 begin 15 if self.v_value is null then 16 self.v_value := value; 17 end if; 18 return odciconst.success; 19 end; 20 21 member function odciaggregateterminate 22 ( 23 self in any_value_string_t, 24 returnvalue out varchar2, 25 flags in number 26 ) return number is 27 begin 28 returnvalue := self.v_value; 29 return odciconst.success; 30 end; 31 32 member function odciaggregatemerge 33 ( 34 self in out any_value_string_t, 35 ctx2 in any_value_string_t 36 ) return number is 37 begin 38 if self.v_value is null then 39 self.v_value := ctx2.v_value; 40 end if; 41 return odciconst.success; 42 end; 43 44 end; 45 / Type body created. SQL> begin 2 $IF DBMS_DB_VERSION.ver_le_18 $THEN 3 execute immediate q''create function any_value (p_value varchar2) return varchar2 4 parallel_enable 5 aggregate using any_value_string_t;''; 6 execute immediate q''grant execute on any_value to public''; 7 execute immediate q''create public synonym any_value for any_value''; 8 $ELSE 9 raise_application_error(-20000,'ANY_VALUE is now supported by Oracle'); 10 $END 11 end; 12 / PL/SQL procedure successfully completed.

Now any_value can be used just like any built-in aggregate function:

 SQL> conn hr/hr Connected. SQL> select banner from v$version; BANNER ------------------------------------------------------------------------------------------------------------------------ Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production 1 row selected. SQL> select d.department_id, 2 any_value(d.department_name) department_name, 3 count(*) number_of_employees 4 from employees e, 5 departments d 6 where d.department_id = e.department_id 7 group by d.department_id; DEPARTMENT_ID DEPARTMENT_NAME NUMBER_OF_EMPLOYEES ------------- -------------------- ------------------- 10 Administration 1 20 Marketing 2 30 Purchasing 6 40 Human Resources 1 50 Shipping 45 60 IT 5 70 Public Relations 1 80 Sales 34 90 Executive 3 100 Finance 6 110 Accounting 2 11 rows selected.

PL/SQL Attributes in User-Defined SUBTYPES

I filed a ticket in MOS for this issue; I’ll update this post with the status

In the previous posts I wrote about non-persistable types (a new feature of Oracle 18c), and about the ability to use PL/SQL types as attributes of non-persistable types (a new feature of Oracle 21c).

It seems that the latter is not fully supported yet for type inheritance.

We can define a type with PL/SQL attributes, and then create a sub-type that adds pure SQL attributes. No problem with this, and the PL/SQL attributes are inherited.

 21c> create type supertype as object ( 2 x pls_integer, 3 y boolean 4 ) 5 not final 6 not persistable 7 / Type created. 21c> create type subtype 2 under supertype ( 3 z integer 4 ) 5 / Type created. 21c> declare 2 obj1 subtype; 3 obj2 supertype; 4 begin 5 obj1 := subtype(7, true, 42); 6 obj2 := obj1; 7 dbms_output.put_line(obj2.x); 8 end; 9 / 7 PL/SQL procedure successfully completed.

But if we try to add new PL/SQL attributes when creating a subtype it fails.

 21c> create type subtype2 2 under supertype ( 3 z pls_integer 4 ) 5 /  Warning: Type created with compilation errors. 21c> show err Errors for TYPE SUBTYPE2: LINE/COL ERROR -------- ----------------------------------------------------------------- 3/7 PLS-00530: Illegal type used for object type attribute: 'PLS_INTEGER'.

My guess is that it happens due to a too strict check in the CREATE TYPE statement – looking for an explicit NOT PERSISTABLE declaration. But it’s impossible to make such a declaration, because, by definition, a subtype inherits the persistability attribute of its supertype, and cannot override it.

 21c> create type subtype2 2 under supertype ( 3 z pls_integer 4 ) 5 not persistable 6 /  Warning: Type created with compilation errors. 21c> show err Errors for TYPE SUBTYPE2: LINE/COL ERROR -------- ----------------------------------------------------------------- 0/0 PLS-00772: PERSISTABLE or NOT PERSISTABLE not permitted with UNDER clause

PL/SQL Attributes in User-Defined Types

In the previous post I wrote about non-persistable types – a feature that was added in Oracle 18c and provides a declarative way to prohibit the use of a schema-level user-defined type in the data model and to limit it to the code only.
Oracle 21c adds another enhancement, and allows to use unique PL/SQL predefined types (i.e., BOOLEAN, PLS_INTEGER/BINARY_INTEGER and their predefined subtypes) as attributes of non-persistable schema-level types.

To demonstrate it I’ll change COLOR_T – the type I used in the examples in the previous post. Let’s declare the r, g and b attributes as BINARY_INTEGER instead of NUMBER(3), and add a BOOLEAN attribute – is_favorite.
Trying to do it for a persistable type (in version 21 or before) fails with the PLS-00530 error:

 SQL> create type color_t as object ( 2 r binary_integer, 3 g binary_integer, 4 b binary_integer, 5 is_favorite boolean, 6 member function hex_code return varchar2 7 ) 8 /  Warning: Type created with compilation errors. SQL> sho err Errors for TYPE COLOR_T: LINE/COL ERROR -------- ----------------------------------------------------------------- 2/5 PLS-00530: Illegal type used for object type attribute: 'BINARY_INTEGER'. 3/5 PLS-00530: Illegal type used for object type attribute: 'BINARY_INTEGER'. 4/5 PLS-00530: Illegal type used for object type attribute: 'BINARY_INTEGER'. 5/15 PLS-00530: Illegal type used for object type attribute: 'BOOLEAN'.

And we’ll get the same error if we try to do it for a non-persistable type in versions before 21c:

 19c> create type color_t as object ( 2 r binary_integer, 3 g binary_integer, 4 b binary_integer, 5 is_favorite boolean, 6 member function hex_code return varchar2 7 ) 8 not persistable 9 /  Warning: Type created with compilation errors. 19c> show err Errors for TYPE COLOR_T: LINE/COL ERROR -------- ----------------------------------------------------------------- 2/5 PLS-00530: Illegal type used for object type attribute: 'BINARY_INTEGER'. 3/5 PLS-00530: Illegal type used for object type attribute: 'BINARY_INTEGER'. 4/5 PLS-00530: Illegal type used for object type attribute: 'BINARY_INTEGER'. 5/15 PLS-00530: Illegal type used for object type attribute: 'BOOLEAN'.

In Oracle 21c it succeeds:

 21c> create type color_t as object ( 2 r binary_integer, 3 g binary_integer, 4 b binary_integer, 5 is_favorite boolean, 6 member function hex_code return varchar2 7 ) 8 not persistable 9 / Type created. 21c> create type body color_t as 2 member function hex_code return varchar2 3 is 4 begin 5 return '#' || to_char(self.r, 'fm0x') || 6 to_char(self.g, 'fm0x') || 7 to_char(self.b, 'fm0x'); 8 end hex_code; 9 end color_t; 10 / Type body created.

Using Non-Persistable Types

Non-persistable types, in general, can be used in both PL/SQL and SQL.
But if the type contains unique PL/SQL attributes its usage is restricted to PL/SQL only.

 21c> create function f (i_color in color_t) return varchar2 as 2 begin 3 if i_color.is_favorite then 4 return i_color.hex_code; 5 else 6 return 'I do not like this color'; 7 end if; 8 end; 9 / Function created. 21c> declare 2 l_yellow color_t := color_t(255, 255, 0, false); 3 l_navy color_t := color_t(0, 0, 128, true); 4 begin 5 dbms_output.put_line('Yellow: ' || f(l_yellow)); 6 dbms_output.put_line('Navy: ' || f(l_navy)); 7 end; 8 / Yellow: I do not like this color Navy: #000080 PL/SQL procedure successfully completed.

If we try to use it in a SQL statement, we get the ORA-39972 error:

 21c> select count(*) from color_tt(); select count(*) from color_tt()  * ERROR at line 1: ORA-39972: cannot use an object type with PL/SQL unique attributes in a SQL statement

Using such types in SQL is not supported even where unique PL/SQL types are allowed: 

 21c> declare 2 l_yellow color_t := color_t(255, 255, 0, false); 3 l_text varchar2(100); 4 begin 5 select f(l_yellow) into l_text from dual; 6 dbms_output.put_line('Yellow: ' || l_text); 7 end f; 8 / select f(l_yellow) into l_text from dual;  * ERROR at line 5: ORA-06550: line 5, column 12: PL/SQL: ORA-39972: cannot use an object type with PL/SQL unique attributes in a SQL statement ORA-06550: line 5, column 5: PL/SQL: SQL Statement ignored

Compare the previous example with the following one – showing that binding unique PL/SQL types to SQL is allowed (as of Oracle 12.1).

 21c> create or replace function f (i_bool in boolean) return varchar2 as 2 begin 3 return case i_bool when true then 'Yes' else 'No' end; 4 end f; 5 / Function created. 21c> declare 2 l_true boolean := true; 3 l_text varchar2(100); 4 begin 5 select f(l_true) 6 into l_text 7 from dual; 8 dbms_output.put_line('Do I like this color? ' || l_text); 9 end; 10 / Do I like this color? Yes PL/SQL procedure successfully completed.

Can any predefined PL/SQL type be used?

All the predefined PL/SQL types and subtypes can be used when creating non-persistable collection types (i.e., varrays and nested tables).
However, when creating non-persistable object types, we cannot use subtypes that are restricted with the NOT NULL constraint (like NATURALN or SIMPLE_INTEGER, for example).
The reason is that it is impossible to define attributes of object types as NOT NULL.

 21c> create type simple_int_tt as 2 table of (simple_integer) 3 not persistable 4 / Type created. 21c> create type object_t as object ( 2 a binary_integer, 3 b simple_integer, 4 c boolean 5 ) 6 not persistable 7 /  Warning: Type created with compilation errors. 21c> show err Errors for TYPE OBJECT_T: LINE/COL ERROR -------- ----------------------------------------------------------------- 3/3 PLS-00218: a variable declared NOT NULL must have an initialization assignment

I think the error message here is a bit misleading, because there is no way to define an initialization assignment

Non Persistable Types

The ability to create user-defined types in Oracle is very powerful and is supported for many years (since Oracle 8).
It allows us to extend the built-in data types and adjust them to our specific needs, and to use them in our data model and in our code.
In the data model they can be used to define the type of specific columns or the type of tables (“object tables”).
In the code they can be used in SQL statements and in PL/SQL.

I’m not a big fan of using user-defined types in the data model.
I am, however, a huge fan of using them in the code.

Here are a few examples for posts in which user-defined types are used in the code:

So whenever I create a user-defined type my intention is that it will be used only in the code.
If the type is created in a PL/SQL scope, then my intention is enforced by definition.
But what if the type is created in the schema level? Can I enforce my intention? As of version 18c I can.

Persistability

A new feature in Oracle 18c allows to define a user-defined type as either persistable (which is the default) or not persistable.
A persistable type can be used in the code and in the data model, just like any type before 18c.
Let’s create color_t and color_tt as persistable types:

 SQL> create type color_t as object ( 2 r number(3), 3 g number(3), 4 b number(3), 5 member function hex_code return varchar2 6 ) 7 / Type created. SQL> SQL> create type body color_t as 2 member function hex_code return varchar2 3 is 4 begin 5 return '#' || to_char(self.r, 'fm0x') || 6 to_char(self.g, 'fm0x') || 7 to_char(self.b, 'fm0x'); 8 end hex_code; 9 end color_t; 10 / Type body created. SQL> create type color_tt as 2 table of color_t 3 / Type created.

We can use persistable types in the data model.
As the type of an object table:

 SQL> create table colors of color_t; Table created.

As the type of a column:

 SQL> create table people ( 2 first_name varchar2(20), 3 last_name varchar2(30), 4 eye_color color_t 5 ); Table created.

And we can use persistable types in the code.
In PL/SQL:

 SQL> declare 2 v_yellow color_t := color_t(255,255,0); 3 v_navy color_t := color_t(0,0,128); 4 begin 5 dbms_output.put_line('Yellow: ' || v_yellow.hex_code); 6 dbms_output.put_line('Navy: ' || v_navy.hex_code); 7 end; 8 / Yellow: #ffff00 Navy: #000080 PL/SQL procedure successfully completed.

In SQL:

 SQL> select * 2 from color_tt( 3 color_t(0,0,0), 4 color_t(255,255,255)); R G B ---------- ---------- ---------- 0 0 0 255 255 255 
 SQL> drop table people; Table dropped. SQL> drop table colors; Table dropped. SQL> drop type color_tt; Type dropped. SQL> drop type color_t; Type dropped.

A non persistable type can be used only in the code.
If we try to use it as the type of an object table or a column, we get the ORA-22384 exception.
Let’s create color_t and color_tt as non-persistable types:

 SQL> create type color_t as object ( 2 r number(3), 3 g number(3), 4 b number(3), 5 member function hex_code return varchar2 6 ) 7 not persistable 8 / Type created. SQL> SQL> create type body color_t as 2 member function hex_code return varchar2 3 is 4 begin 5 return '#' || to_char(self.r, 'fm0x') || 6 to_char(self.g, 'fm0x') || 7 to_char(self.b, 'fm0x'); 8 end hex_code; 9 end color_t; 10 / Type body created. SQL> create type color_tt as 2 table of (color_t) 3 not persistable 4 / Type created.

We cannot use non-persistable types in the data model:

 SQL> create table colors of color_t; create table colors of color_t * ERROR at line 1: ORA-22384: cannot create a column or table of a non-persistable type SQL> create table people ( 2 first_name varchar2(20), 3 last_name varchar2(30), 4 eye_color color_t 5 ); create table people ( * ERROR at line 1: ORA-22384: cannot create a column or table of a non-persistable type

We can use non-persistable types in the code, in PL/SQL and in SQL:

 SQL> declare 2 v_yellow color_t := color_t(255,255,0); 3 v_navy color_t := color_t(0,0,128); 4 begin 5 dbms_output.put_line('Yellow: ' || v_yellow.hex_code); 6 dbms_output.put_line('Navy: ' || v_navy.hex_code); 7 end; 8 / Yellow: #ffff00 Navy: #000080 PL/SQL procedure successfully completed. SQL> SQL> select * 2 from color_tt( 3 color_t(0,0,0), 4 color_t(255,255,255)); R G B ---------- ---------- ---------- 0 0 0 255 255 255 
 SQL> drop type color_tt; Type dropped. SQL> drop type color_t; Type dropped.

Type Dependency

As you may expect, a persistable type cannot depend on non-persistable types.

 SQL> create type color_t as object ( 2 r number(3), 3 g number(3), 4 b number(3) 5 ) 6 not persistable 7 / Type created. SQL> create type color_tt as table of color_t 2 / Warning: Type created with compilation errors. SQL> show err Errors for TYPE COLOR_TT: LINE/COL ERROR -------- ----------------------------------------------------------------- 0/0 ORA-22383: a persistable type cannot have non-persistable attributes

A non-persistable type can depend on both persistable and non-persistable types.

 SQL> create or replace type color_tt as 2 table of (color_t) 3 not persistable 4 / Type created.

Note: the parenthesis are required when creating a collection type with the persistability clause

 SQL> drop type color_tt; Type dropped. SQL> drop type color_t; Type dropped.

Type Inheritance

A subtype inherits the persistability attribute of its supertype, and cannot override it. 

 SQL> create type color_t as object ( 2 r number(3), 3 g number(3), 4 b number(3) 5 ) 6 not final 7 not persistable 8 / Type created. SQL> create type rgba_color_t 2 under color_t 3 (alpha number(3)) 4 persistable 5 / Warning: Type created with compilation errors. SQL> show err Errors for TYPE RGBA_COLOR_T: LINE/COL ERROR -------- ----------------------------------------------------------------- 0/0 PLS-00772: PERSISTABLE or NOT PERSISTABLE not permitted with UNDER clause SQL> create type rgba_color_t 2 under color_t 3 (alpha number(3)) 4 / Type created. SQL> select type_name,persistable 2 from user_types; TYPE_NAME PERSISTABLE -------------------- -------------------- COLOR_T NO RGBA_COLOR_T NO

In the next post I’ll write about an enhancement that was added to non-persistent types in Oracle 21c.