Oracle Open World 2011: Oracle Database 11g Features for Developers by Connor McDonald

Yesterday I had the pleasure of attending a session by Connor McDonald. I heard a lot about him, his presentation style, and I even did a book review back in 2005.
Everybody I talk to who attended a session by Connor – ever – is always very positive on his presentation style. And it is very impressive, humor, technical knowledge, all the ingredients are there to keep you focused on the content.
The opening slide, he put on as you walked in the room invited you to move forward as the presentation has code sample in a fontsize which he showed on the slide. The room filled up, and it was a large room. There was another benefit of being in the front of the room, apart from being able to read the slides, he handed out chocolates – starting from the front. Talking to Connor later in the evening, he told me it takes months to prepare for a presentation, and it really shows. If you get a chance to see Connor do a presentation, attend it and make sure to arrive early!
Part of the presentation was on Edition Based Redefinition, and because I presented on that subject before I believed there was a flaw in his presentation. But there was not, I was mistaken. The rest of this blogpost shows you where I was mistaken, and Connor was absolutely right.

When creating an editioning view, I believed it was “required” to name the columns in your view instead of using the wildcard “*” to select the columns. Shielding the editioning view from table alterations. So let’s start with an edition enabled user to setup our table.

SQL> conn alex/alex@xe11
Connected.
SQL> alter user alex enable editions
  2  /

User altered.

SQL> select *
  2    from v$version
  3  /

BANNER
---------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

5 rows selected.

SQL> create table "_EMP"
  2  (empno number
  3  ,ename varchar2(20)
  4  )
  5  /

Table created.

As you can see in the above code snippet, I run this test on Oracle Database 11g Express Edition. The tablename I picked up from Connor’s session. Naming your table something freakish will (hopefully) stop developers from using the table directly in their code. I like that naming convention. Let’s continue with two editions

SQL> create edition r1 as child of ora$base
  2  /

Edition created.

SQL>
SQL> create edition r2 as child of r1
  2  /

Edition created.

In each of the editions we will create an editioning view using the wildcard “*” to select the columns

SQL> alter session set edition = r1
  2  /

Session altered.

SQL> create or replace
  2  editioning view emp
  3  as
  4  select *
  5    from "_EMP"
  6  /

View created.

SQL>
SQL> alter session set edition = r2
  2  /

Session altered.

SQL> create or replace
  2  editioning view emp
  3  as
  4  select *
  5    from "_EMP"
  6  /

View created.

SQL>

I believed, wrongly, that the wildcard “*” would be dynamic, meaning that if the table definition would change both editioning view would include the new column as well. This is not the case.The editioning views don’t even get invalidated…

SQL> alter session set edition = ora$base
  2  /

Session altered.

SQL> alter table "_EMP"
  2  add something varchar2(10)
  3  /

Table altered.

SQL> select owner
  2       , object_name
  3      , status
  4    from all_objects_ae
  5   where object_name = 'EMP'
  6  /

OWNER                          OBJECT_NAME                    STATUS
------------------------------ ------------------------------ -------
ALEX                           EMP                            VALID
ALEX                           EMP                            VALID

2 rows selected.

Even though they don’t get invalidated, the definition of the editioning views surely must be changed then….

SQL>
SQL> alter session set edition = r1
  2  /

Session altered.

SQL> desc emp
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- ----------------------

 EMPNO                                                                      NUMBER
 ENAME                                                                      VARCHAR2(20)

SQL>
SQL> alter session set edition =  r2
  2  /

Session altered.

SQL> desc emp
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- ----------------------

 EMPNO                                                                      NUMBER
 ENAME                                                                      VARCHAR2(20)

SQL>

No. The definition includes the columns which existed before we altered the table.
What is stored in the metadata, you may wonder. Let’s go and find out:

SQL> alter session set edition = r1
  2  /

Session altered.

SQL> set long 50000
SQL> select dbms_metadata.get_ddl ('VIEW'
  2                               ,'EMP'
  3                                                      )
  4    from dual
  5  /

DBMS_METADATA.GET_DDL('VIEW','EMP')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONING VIEW "ALEX"."EMP" ("EMPNO", "ENAME") AS
  select "EMPNO","ENAME"
  from "_EMP"


1 row selected.

SQL>
SQL> alter session set edition =  r2
  2  /

Session altered.

SQL> select dbms_metadata.get_ddl ('VIEW'
  2                               ,'EMP'
  3                                                      )
  4    from dual
  5  /

DBMS_METADATA.GET_DDL('VIEW','EMP')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONING VIEW "ALEX"."EMP" ("EMPNO", "ENAME") AS
  select "EMPNO","ENAME"
  from "_EMP"


1 row selected.

As you can see in the output above, the actual column names are in the metadata, shielding changes to the editioning views when the table definition changes.
Connor was absolutely right about the mechanics of Edition Based Redefinition. Does this mean you should use the wildcard “*”in your editioning views? I believe not, I believe you should explicitly name the columns that you want to include in a specific edition. This way it is more clear to the developers, and DBA alike. Rerunning the scripts might all of sudden include columns in an edition which weren’t suppose to be there. When you explicitly name your columns, you won’t have this problem.

Just the last bit, cleaning up my environment:

SQL> alter session set edition = ora$base
  2  /

Session altered.

SQL> drop edition r2 cascade
  2  /

Edition dropped.

SQL> drop edition r1 cascade
  2  /

Edition dropped.

SQL> drop table "_EMP" purge
  2  /

Table dropped.