I was going to explain about the edition object in this post, but in looking at the existing views around this object, dba_editions and dba_edition_comments, it seemed like they were missing some crucial info also. The 3rd new view I call dba_edition_info, it adds the columns of these 2 views together and includes a column that shows the create order of the editions. I used to generate an ordered list of the editions with SQL like this:
SELECT edition_name FROM dba_editions START WITH edition_name = ‘ORA$BASE’ CONNECT BY PRIOR edition_name = parent_edition_name ORDER BY LEVEL;
That works, but it adds a lot of complexity since we can also derive this from the base data dictionary table. It’s possible my new view will stop working correctly if Oracle adds new capabilities, such as the ability to have more than one child edition linked to a single parent, but for now the editions present as a single chain, a linked list represented in a table. This restriction of a single child for any parent presents some unique challenges for the process I use for deploying new code, and I hope that it is lifted at some point in the future, but until then I’m confident in recommending this view as a better way to get all the information about the edition objects in the database.
The query above can be re-written much more simply with the new view:
SELECT edition_name FROM dba_edition_info ORDER BY create_order;
Here is the source code to create the dba_edition_info view. Run this as sys, then you can query the resulting view as you would any other sys view.
create or replace view sys.dba_edition_info (edition_name, create_order, parent_edition_name, usable, comments)
as
select o.name, utl_raw.length(e.code), po.name, decode(bitand(e.flags,1),1,’NO’,’YES’), c.comment$
from sys.obj$ o, sys.edition$ e, sys.obj$ po, sys.com$ c
where o.obj# = e.obj# and po.obj# (+)= e.p_obj# and o.obj# = c.obj# (+);
comment on table SYS.DBA_EDITION_INFO is ‘Describes all editions in the database’;
comment on column SYS.DBA_EDITION_INFO.EDITION_NAME is ‘Name of the edition’;
comment on column SYS.DBA_EDITION_INFO.CREATE_ORDER is ‘Edition order’;
comment on column SYS.DBA_EDITION_INFO.PARENT_EDITION_NAME is ‘Name of the parent edition for this edition’;
comment on column SYS.DBA_EDITION_INFO.USABLE is ‘A value of ”YES” means edition is usable and ”NO” means unusable’;
comment on column SYS.DBA_EDITION_INFO.COMMENTS is ‘Edition comment’;
create or replace public synonym dba_edition_info for sys.dba_edition_info;