Category: PL/SQL

EBR – Part 11: Database-Level Default Edition

This is part 11 of a post series about Oracle Edition-Based Redefinition.

Visit the index page for all the parts of the series

Introduction

As we’ve seen in the previous posts, the process of an online application upgrade, in high level, includes the following steps:

  • Creating a new edition
  • Applying all the necessary changes in the privacy of the new, unexposed, edition
  • Making sure that all the objects in the new edition are valid, and that all the objects that should be actual in the new edition are indeed actual
  • Exposing the new edition to the clients

How do we expose the new edition?

In the previous examples I did it by explicitly changing the client’s session edition, from the client session itself. But that was just for demo purposes, and it is certainly not the recommended way.
The best way to expose new editions, in my opinion, is by using services. This gives us high levels of both flexibility and control.
I’ll dedicate the next post for exposing new editions via services.
In this post I’ll show another option – Database Default Edition. This method is very simple, but also very limiting, and therefore it’s suitable only for very specific cases.

Database Default Edition

There is always one database-level default edition. We can see it using this query: Continue reading “EBR – Part 11: Database-Level Default Edition”

EBR – Part 9: Adding a New Column

This is part 9 of a post series about Oracle Edition-Based Redefinition.

Visit the index page for all the parts of the series

Introduction

In part 7 (“Editioning Views”) I introduced our third development use case – adding a new column that represents a new business logic.

I emphasize the fact it’s a new business logic, because sometimes we add new columns that come instead of existing columns, for replacing an existing business logic. As we’ll see in future posts, such cases require some additional handling in online upgrades.

Now, after part 8 (“The Last Planned Downtime”), in which we converted our schema to use editioning views and made it fully ready for online upgrades, we can implement the requirement in an online way.

The Task

We want to add a new attribute – Eye Color – to the Person entity.
For that we need to add a new column – EYE_COLOR – to the PEOPLE table, add make the corresponding changes in the PEOPLE_DL and APP_MGR packages (adding an input parameter – i_eye_color – to the people_dl.add procedure and changing the implementation of people_dl.add and app_mgr.do_something accordingly).

The Current State

At this point, V2 is our latest edition and it is exposed to the production clients. When connected to V2, we see the following objects: Continue reading “EBR – Part 9: Adding a New Column”

EBR – Part 8: The Last Planned Downtime

This is part 8 of a post series about Oracle Edition-Based Redefinition.

Visit the index page for all the parts of the series

Introduction

I concluded the previous post by stating that the application code should never reference tables directly; instead, every table should be covered by an editioning view and the application code should reference the views.

Starting from Scratch?

If you start developing a new system from scratch, I strongly recommend to follow this practice, even if initially there is no requirement for online upgrades. That way, you will be ready for online upgrades from day one, so should such a requirement arise at a later stage you will not need to change anything – neither your data structures nor your code. The only overhead is maintaining an editioning view for every table, and I’m sure you’ll realize very quickly that this overhead is negligible. As long as downtime during your upgrades is acceptable, you only need to enable your schema owner for editions (a one-time operation, as shown in part 3), and (by default) you will simply use a single edition – the default one.

Existing Systems

If you have an existing system, and you want to enjoy the benefits of online upgrades and zero downtime, you need to do a one-time conversion of your schema – to cover all the tables by editioning views. Actually, even if you have an existing system and you do not want to enjoy these benefits, I would still recommend getting ready for online upgrades now. The rationale is the same as in the previous section (“Starting from Scratch?”).

Converting the Demo Schema

Let’s handle first the demo schema I’ve been using in this post series. We currently have the following objects: Continue reading “EBR – Part 8: The Last Planned Downtime”

EBR – Part 7: Editioning Views

This is part 7 of a post series about EBR.

Visit the index page for all the parts of the series

Introduction

Our next use case is adding a column that represents a new logic to the PEOPLE table, and making the corresponding changes in the PEOPLE_DL and APP_MGR packages. Of course, as we speak about EBR, the upgrade from the previous version to the new one should be online.
An online upgrade means that the application users should be able to continue working uninterruptedly. The code objects that they use should remain valid and available at any time.
In addition to the challenges raised from the first and second use cases, the current use case introduces another challenge. And unlike the previous challenges, this one cannot be overcome just by using a new edition.

The Current State

At this point, after completing two upgrades already – in part 3 and part 5, we have three editions, with the following actual objects:

V2> select object_name,object_type,status, edition_name
  2  from   user_objects
  3  order  by object_name,object_type;

OBJECT_NAME  OBJECT_TYPE  STATUS  EDITION_NAME
------------ ------------ ------- ------------
APP_MGR      PACKAGE      VALID   ORA$BASE
APP_MGR      PACKAGE BODY VALID   V2
PEOPLE       TABLE        VALID
PEOPLE_DL    PACKAGE      VALID   V2
PEOPLE_DL    PACKAGE BODY VALID   V2
PEOPLE_PK    INDEX        VALID

6 rows selected.

As discussed in part 6, a table is a non-editioned object, and therefore we have a single instance of the PEOPLE table – referenced by all the instances of the PEOPLE_DL package body in all the editions.

The Task

We want to add a new attribute – Eye Color – to the Person entity.
For that we need to add a new column – EYE_COLOR – to the PEOPLE table, add make the corresponding changes in the PEOPLE_DL and APP_MGR packages (adding an input parameter – i_eye_color – to the people_dl.add procedure and changing the implementation of people_dl.add and app_mgr.do_something accordingly).

Our Problem

If we try to add the new column to the table, then Continue reading “EBR – Part 7: Editioning Views”

EBR – Part 6: Editionable and Non-Editionable, Editioned and Non-Editioned

This is part 6 of a post series about EBR.
In part 1 we created the baseline model and code – a table (PEOPLE) and two packages (PEOPLE_DL and APP_MGR).
In part 2 we saw that even a simple change – a package body compilation – can be dangerous in a busy system.
In part 3 we learned about editions and how they can be used for solving the problems described in part 2, so changes can be applied in both online and safe way. I discussed enabling, creating and using editions; session edition; and editioned objects.
In part 4 we discussed another challenge of online upgrades – invalidation of dependent objects.
In part 5 we saw the importance of explicit actualization of dependent objects.
In this part we’ll dive into the differences between editioned and non-editioned objects, editionable and non-editionable object types, and the relationships between them.

Visit the index page for all the parts of the series

In a previous post, we enabled the user DEMO_EBR for editions. As a result, some of the existing objects of DEMO_EBR (the package specs and package bodies) have become editioned, and the others (a table and an index) have not. Why is that?
Before I answer this question, let’s see what an editioned object is exactly, and what a non-editioned object is.

Editioned and Non-Editioned Objects

An editioned object is Continue reading “EBR – Part 6: Editionable and Non-Editionable, Editioned and Non-Editioned”

COLLECT INTO inside a Cursor Definition?

I have been programming in PL/SQL since 1993, more or less continuously (I’ve just realized that it’s exactly half of my life until now), so I know the PL/SQL syntax quite well.
So when I saw the following piece of code a few days ago, I was willing to bet it would not compile:

declare
    v_number_array sys.odcinumberlist;
begin
    for v_rec in (select rownum as num
                  bulk   collect
                  into   v_number_array
                  from   dual
                  connect by level <= 5)
    loop
        dbms_output.put_line(v_rec.num);
    end loop;
end;

Note it's a Cursor FOR LOOP that includes a BULK COLLECT INTO clause inside the cursor definition.

Luckily, I did not bet. I would have lost... Continue reading "COLLECT INTO inside a Cursor Definition?"

EBR – Part 5: Explicit Actualization of Dependent Objects

This is part 5 of a post series about EBR.
In part 1 we created the baseline model and code – a table (PEOPLE) and two packages (PEOPLE_DL and APP_MGR).
In part 2 we saw that even a simple change – a package body compilation – can be dangerous in a busy system.
In part 3 we learned about editions and how they can be used for solving the problems described in part 2, so changes can be applied in both online and safe way. I discussed enabling, creating and using editions; session edition; and editioned objects.
In part 4 we discussed another challenge of online upgrades – invalidation of dependent objects.
In this part I’ll demonstrate everything I wrote about in the previous post.

Visit the index page for all the parts of the series

Creating a New Edition

We want to change the PEOPLE_DL spec, and as in any online upgrade, we start by creating a new edition. Let’s create edition V2 and grant use on it to DEMO_EBR:

-- connected as system
ORA$BASE> create edition v2;

Edition created.

ORA$BASE> grant use on edition v2 to demo_ebr;

Grant succeeded.

Invalidation of Dependent Objects

Before doing the online upgrade properly (i.e., using the new edition), let’s see what happens in a non-EBR environment.
First let’s see the objects in our schema and the dependencies between them before the upgrade: Continue reading “EBR – Part 5: Explicit Actualization of Dependent Objects”

EBR – Part 4: Invalidation and Actualization of Dependent Objects

This is part 4 of a post series about EBR.
In part 1 we created the baseline model and code – a table (PEOPLE) and two packages (PEOPLE_DL and APP_MGR).
In part 2 we saw that even a simple change – a package body compilation – can be dangerous in a busy system.
In part 3 we learned about editions and how they can be used for solving the problems described in part 2, so changes can be applied in both online and safe way. I discussed enabling, creating and using editions; session edition; and editioned objects.
In this part we’ll see more challenges that online upgrades bring – this time when changing a package spec.

Visit the index page for all the parts of the series

Invalidation

This time we need to change the PEOPLE_DL package spec. There are no table changes, and of course, as we speak about EBR, the upgrade from the previous version to the new one should be online.

An online upgrade means that the application users should be able to continue working uninterruptedly. The code objects that they use should remain valid and available at any time.

In addition to the challenges raised from the first use case – changing PEOPLE_DL package body – the current use case introduces another challenge: invalidation. Changing the PEOPLE_DL package spec will cause its dependent objects become invalid.
Even if the change does not cause any compilation errors in the dependent objects, they become invalid.
Actually, even if we just recompile an object, without making any changes in it, its dependent objects still become invalid.
Now, it’s true that in these cases revalidation will occur automatically as soon as the invalid object is used, but in an online upgrade scenario this is usually unacceptable.

Moreover, many times we need to change multiple interrelated objects. For example, changing the API of some procedure in one package, and consequently changing it in the package body and changing the calls to that procedure from another package. In this case we cannot avoid having broken objects – invalid and with compilation errors – during the upgrade, and this is obviously cannot be really considered an online upgrade. Continue reading “EBR – Part 4: Invalidation and Actualization of Dependent Objects”

EBR – Part 3: Editions and Editioned Objects

This is part 3 of a post series about EBR.
In part 1 we created the baseline model and code – a table (PEOPLE) and two packages (PEOPLE_DL and APP_MGR).
In part 2 we saw that even a simple change – such as a package body compilation – can be dangerous in a busy system.
In this post we’ll see how EBR solves the problems described in the previous post, so such a change can be applied in both online and safe way.

Visit the index page for all the parts of the series

Overview

The basic concept of EBR is that while the clients are connected to the database and use the objects – say the package pkg from the previous post – we can create another instance of pkg – a copy of pkg – and make all the necessary changes in this new instance.
We create this new pkg in the same schema, so now we actually have two packages named pkg in one schema. But each session sees only one of these packages – the clients continue seeing the old instance of pkg, and we the developers currently see the new instance of pkg.
When we are happy with the new implementation, we can expose it, so clients that will open new connections to the database from now on, will see the new pkg.
We achieve this by editions. Imagine that our schema is divided into multiple separate areas. At any point in time, each session sees one and only one area.
These separate areas are called editions, and we can keep basically any code object there – procedures, functions, packages, triggers, views, and more.
We can’t have two objects named pkg in the same edition, but we can in two separate editions.

Editions

Editions are database-level objects Continue reading “EBR – Part 3: Editions and Editioned Objects”

EBR – Part 2: Locking, Blocking and ORA-04068

This is part 2 of a post series about EBR.
In part 1 we created the baseline model and code – a table (PEOPLE) and two packages (PEOPLE_DL and APP_MGR).
In this post we’ll start handling the first type of change request: changing a package body.

Visit the index page for all the parts of the series

The Task

We need to change the implementation of the PEOPLE_DL package; i.e. we need to change the package body.
There are no API changes (the package spec is not changed) and no table changes.
And of course, as we speak about EBR, the upgrade from the base version to the new one should be online.

The Problems

Locking and Blocking

An online upgrade means Continue reading “EBR – Part 2: Locking, Blocking and ORA-04068”