My First Decade in the International Oracle Community

Exactly ten years ago, in November 2014, I gave my first presentation at a conference outside my country and not in my native language.

At that point, I already had over twenty years of career experience in software development, focusing on databases – my long-lasting passion. I’ve always believed in knowledge sharing, and during those years, I enjoyed giving occasional presentations – sharing from what I learned and from the experience I gained – but I always presented in Hebrew and always in Israel: in companies I worked for, in the historical DBA Forum of ilOUG (Israel Oracle User Group) and at some local conferences.

Sharing and Learning

I discovered that when you build a presentation and prepare to deliver it, you learn a tremendous amount yourself. For example, in 2013, I took it upon myself to deliver a full-day seminar on the new features for developers in Oracle 12c. Since this version had just been released at the time, no one here had the experience and knowledge – and consequently, the desire – to build and deliver such a seminar in Israel. I volunteered to do it and spent many days preparing the seminar, during which I learned a lot. It’s likely that without the goal of preparing and delivering the seminar, I wouldn’t have learned so much new (and useful) content in a relatively short time period and at such a deep level.
To learn, I also used materials published by members of the international Oracle community – both Oracle users and Oracle employees.
Here’s a post I published then, containing the list of topics I included in that seminar.

I regularly read materials shared by community members (and learned a lot from them), and I heard about user group conferences around the world.
I realized I could develop more professionally, learn more, and share my knowledge with more people if I stepped out of my comfort zone, traveled to conferences worldwide, and presented in English – a language I naturally find more challenging to express myself in.
It wasn’t easy, it was even scary, but I took heart and decided to try. I submitted abstracts to several conferences, and finally two of them got accepted.

My Trial by Fire

And so, in November 2014, I arrived in Nuremberg for the DOAG conference, one of the largest Oracle user group conferences in the world.
It was a fascinating experience, somewhat frightening, and very enlightening.

I understood that this step meant much more than I had thought.

For instance, the opportunity to meet product managers face-to-face – the people directly responsible for the software and features I use. To talk with them, consult with them, complain to them. Even to have lunch with them, like what happened with Bryn Llewellyn, who was then the product manager for PL/SQL and EBR.

The first presentation I gave at that conference (and as mentioned, my first-ever presentation abroad, in English, and in front of a large audience) was about EBR.

As I was preparing on stage, just before starting the presentation, nervous and excited, someone approached me and said “Excuse me, I’d like to introduce myself: I’m Bryn, the product manager for the topic you’re about to present on.”
Of course, that wasn’t the best antidote for my nerves…

I replied that I knew very well who he was (I had read his articles, seen his videos), and that it was a great honor for me, as well as terrifying. He was incredibly nice and said “I just wanted to say I promise not to interfere, and if you want my help, you’re welcome to ask.”


The presentation went quite well, and afterward, I had lunch with Bryn, where he already solved one problem I had with EBR (how to run a job in a specific edition – you associate the job with a class, the class with a service, and the service with an edition).

Bryn helped me quite a lot afterward, we continued to meet at conferences and exchanged opinions. When he came to Israel he visited my workplace, and he encouraged me to attend Oracle’s big conference in San Francisco – Oracle OpenWorld – and we even gave a joint presentation there, and he was a staunch supporter.

None of this would have happened if I had stayed at home and only learn from what he and others published.

Access to Oracle product managers not only helps solve problems (like the advice I received at that lunch ten years ago) but also allows influencing the product’s future. There’s a lot of listening to users that translates into actions.
An example from my experience is the USER_TRIGGERS_AE dictionary view that was added in version 19c.
Another example is the support of both pre-update and post-update values in the DML RETURNING clause that was added in version 23ai.

The Community

Since DOAG 2014, I’ve participated in dozens of conferences in about 20 countries. I became part of a community of hundreds of people from around the world, with shared interests, united most of all by their desire to contribute to others, share their knowledge and experience, and collaborate with each other.

The Oracle ACE program has a big and important role in building and maintaining this community, and many of us are members of the program. The program recognizes, rewards, and promotes technical experts and community leaders for their contributions to the Oracle community.

Another group I’m a member of is Symposium 42, which was created by the Oracle database community to support the Oracle database community. All members are experts in creating & sharing technical content or organizing community events and activities.

I have many interactions with my community peers on professional topics.
There’s always someone to consult with and get guidance from.
Getting honest feedback on your post or presentation from people whose opinions you greatly value is priceless.
The opportunity to do a joint presentation with someone like Connor is something I wouldn’t have dared to dream about ten years ago.

The Friendship

But this community is much more than just a group of professionals with shared interests. Real friendships form, people support each other, and of course, enjoy drinking and eating together.

In times when there are many troubles and conflicts in the world, it’s heartwarming, and even hopeful, to see people from different countries and different cultures simply sit, drink, chat, and enjoy together.
Here for example – 7 people from 7 different countries:


During this journey that I started ten years ago, I gained a new circle of friends from many countries.
People I used to admire from afar for their knowledge and the way they share it in books, blogs, or videos – I’m proud today to call them friends (and still admire some of them 🙂).

I remember one evening many years ago at OOW in San Francisco, when I ran into Tim on the street, and he noticed I was a bit lost and down, and immediately took me to get something to eat. It was the most natural thing for him, and for me it emphasized how much this community is far beyond just sharing knowledge about features.

And there are many more examples. Here are just a few of them:

The warm hospitality of Alex and his family when I came with my wife and children for a vacation in their country.

The first time I met Erik, when I came to a conference in the Netherlands, and he was so welcoming and warm that in an instant I felt comfortable in the company of people I hadn’t known before.

When Jože heard at a conference in Ireland that I also bake bread, and gave me half of the sourdough starter he had brought for Martin.
Actually, sourdough bread is a serious matter of deep discussions with Martin, Jonathan, Gerald, and others. Which also reminds me of the discussions about pizza ovens with Maria.

Driving a very small car in Slovenia with Heli, Javed and Kamran (once again, each one of us from a different country).

A very unique experience was traveling around India together with my son Nimrod at OCYatra, along with Sai, Mike, Connor, Sandesh, Dimitri, Pedro, and others.

In the last year – a challenging period in my country during which I haven’t traveled – many friends moved me by showing interest and concern for my family’s well-being.

Local User Groups

Many members of the international community are also active in their local user groups, which benefits the various organizations and allows even smaller organizations to bring international-level content to their members.

I, for example, as an active member in the Israeli organization, helped organize conferences and meetups in Israel where speakers from around the world participated.

The crown jewel for me was in 2020, the last conference we held in Israel before COVID, during which I had the honor of hosting my fellow speakers at my home.


I hope to continue being active and contributing more to the community. I’ll update again in 2034 🙂

Optimization of NOT NULL Constraint Creation

Several years ago I wrote a series of 8 posts about constraint creation optimization. I think it’s time to add some more posts to the series.
I showed there, among other things, that Oracle does a nice optimization – that may save plenty of time – when we add in a single ALTER TABLE statement a new (nullable with no default value) column and a corresponding:
– inline (a.k.a. “column-level”) check constraint
or
– foreign key constraint (either inline or out-of-line)

What about NOT NULL constraints?

Oracle has a special optimization for the case of adding a column with a NOT NULL constraint (and no default value).
It checks if the table contains at least one record. If it does, then we cannot add the new column, becuase the NOT NULL constraint will be violated for all the existing rows. If the table doesn’t contain any records, the new column can be added.

Let’s start with an empty table: Continue reading “Optimization of NOT NULL Constraint Creation”

DECODE With Ranges

Tim Hall wrote this post yesterday (as part of Joel Kallman Day 2022) with some examples for “the old way” vs. “the new way” to do some stuff.
One of these examples is DECODE and CASE Expressions. As Tim wrote:

CASE expressions were first released in Oracle 8i […] Prior to that if we wanted something similar to a CASE expression in our SQL, we had to use the DECODE function.

This reminded me of a trick I came up with many years ago, probably before 8i 🙂

A table T has a column X, and we need a query that one of the items it returns is a rank – either “Low”, “Medium” or “High”. This rank is determined by the value of X and two thresholds – LOW_TH and HIGH_TH.

With a (Searched) CASE Expression it’s as simple as this: Continue reading “DECODE With Ranges”

Eurovision Analysis #JoelKallmanDay

Here’s my contribution to Joel Kallman Day 2022.

The picture is from the last time I met Joel. It was taken at ilOUG Tech Days 2020, just before an invigorating keynote by Joel.

Four years ago, as part of ODC Appreciation Day 2018 (as the annual Oracle community appreciation day was called that year), I wrote about Pattern Matching in SQL. That post was not a technical one.

This time I decided to return to the same topic, with a technical post.
I recorded a video that explains Pattern Matching by analyzing the winners of the Eurovision Song Contest over the years.

Pre-19c Crossedition Triggers

As shown in the previous post, finding all the crossedition triggers in the database is easy using DBA_TRIGGERS_AE, but this can be done only as of Oracle 19c – the release in which this data dictionary view was added.

Here are two options that can be used before 19c.

A Proper Way

We can use DBA_OBJECTS_AE to find all the triggers across all editions, and then for each one to “jump” to its edition using dbms_sql and check whether it is a crosseedition trigger or not using DBA_TRIGGERS. Continue reading “Pre-19c Crossedition Triggers”

USER_TRIGGERS_AE

One of the fundamental building blocks of EBR (Edition Based Redefinition) is Crossedition Triggers.

This special type of triggers is used when we change the structure of tables in an online application upgrade, and the data of these tables may be changed while we do the upgrade and afterwards, until no session uses the pre-upgrade edition. These triggers keep the pre-upgrade representation of the data and post-upgrade representation of the data in sync.

Crossedition triggers are temporary in nature – they should be dropped when all the clients use the edition in which the triggers were created (or one of its descendent editions).

In theory, crossedition triggers live for a very short period.

In real life, when we have multiple active editions and different types of clients that may keep using older editions for a while before upgrading to the latest edition, it may take days and even weeks before we can drop the crossedition triggers. In my opinion, this is perfectly fine, and actually one of the benefits that I see in EBR.

Another significant difference between crossedition triggers and regular triggers is that crossedition triggers are not inherited by descendent editions. However, they may be triggered by DML operations that are executed in a descendent edition.

This means that we need a way to check which crossedition triggers exist in our schema/database.
Continue reading “USER_TRIGGERS_AE”

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);