Oracle RDBMS 11gR2 – alter or replace user defined types even when there are dependencies

In our series on the 11g R2 release of the Oracle RDBMS, AMIS staff discusses various functions, features an facilities that have been added, improved or extended in this latest release. These articles are the fruit of several months of studying this new release and projecting which new features and changes would be the most useful to us in our daily practice. This article looks at apparently relatively minor changes in functionality that may well have a substantial impact on the usability of Abstract Data Types (ADTs) or user defined (object) types.

Using ADTs or user defined types in the Oracle RDBMS is quite often very useful. Much more useful by the way than many developers realize. The integration for example between SQL and PL/SQL can be streamlined using user defined types in conjunction with TABLE and MULTISET or the BULK COLLECT operations. Presenting a service-style interface from PL/SQL packages is also much easier realized using custom type definitions – easier both for the definition of the package’s "service contract", the consumer of the package (at least the Database Adapter used in the SOA Suite) and the developers implementing the service contract. Such a contract in terms of nested types quite close resembles the typicall WebService contract that used nested XML documents.

One less agreeable aspect of types (prior to 11gR2 that is) is their administration. Especially the fact that once a type has been created and has been referenced by other types, it cannot be altered. In order to change a small (or big thing) in a single type, it may be necessary to drop a whole bunch of types that are all somehow related to each other. When the types are all recreated, they can be recreated with the required change inside. Types are much like views or packages: database objects that are owned, have associated privileges and synonyms, and add to the general complexity of database applications.

Note: from the administration perspective, it makes quite a difference whether the types are used only for PL/SQL programs and SQL/PLSQL integration – to define the record structure used for the interaction and to create in memory data structures – or whether the types describe the storage structure for columns or nested tables. In the latter case, changing the type has far more impact than in the previous case – where changing types should not have to have a lot of effect.

In 11gR2, the impact of changing types has been brought to where it really should be: changing a type is allowed even if there are dependencies on that type – as long as those dependencies are not from nested tables/columns. Dependencies from PL/SQL program units or views or other types (that are not used for storage structures) are fine and will allow the change of the type (and even a drop of the type). This ability to modify types without recreating the entire nested structure of inter-dependent types will make use of types in PL/SQL applications much more acceptable to DBAs.

I will show you the scenario that up to 11gR2 would cause the nastiness that has now been resolved in 11gR2:

Let’s create a simple type definition:

create or replace
type person_t as object
( first_name varchar2(30)
, last_name  varchar2(5)
)
/

This type can be used in PL/SQL code, in View definitions and for example in simple queries that tools such as SQL*Plus know how to execute:

select person_t('JOHN', 'DOE')
from   dual
/
PERSON_T('JOHN','DOE')(FIRST_NAME, LAST_NAME)
--------------------------------------------------------------------------------
PERSON_T('JOHN', 'DOE')

If we try to create an object with values that do not fit in the type definition, we get errors:

from   dual
/

select person_t('JOHN', 'DOESNT')
                        *
ERROR at line 1:
ORA-22814: attribute or element value is larger than specified in type

It is simple to alter the type – as long as there are no dependencies on this type:

create or replace
type person_t as object
( first_name varchar2(30)
, last_name  varchar2(10)
)
/

And now the same query will yield a result, as now the value DOESNT will fit into the last_name attribute of the PERSON_T type.

select person_t('JOHN', 'DOESNT')
from   dual
/

PERSON_T('JOHN','DOESNT')(FIRST_NAME, LAST_NAME)
--------------------------------------------------------------------------------
PERSON_T('JOHN', 'DOESNT')

I will now create a function that returns an instance of the PERSON_T:

create or replace
function  get_person
return    person_t
as
begin
  return person_t('HELEN', 'TUDOR');
end;

This function can be used in SQL queries like this:

select get_person
from   dual
/

GET_PERSON(FIRST_NAME, LAST_NAME)
--------------------------------------------------------------------------------
PERSON_T('HELEN', 'TUDOR')

Changing the type, to extend the last_name for example:

create or replace
type person_t as object
( first_name varchar2(30)
, last_name  varchar2(30)
)

will invalidate the function, because of its dependency on the type.

select status
from   user_objects
where  object_name = 'GET_PERSON'
/

returns INVALID.

However, since the change in the type is not one that really impacts its ‘contract’ with the function, we can call the function like before and not see any difference. Recompilation of the function is performed automatically.

If we now change the type again, this time to add a birthdate attribute:

create or replace
type person_t as object
( first_name varchar2(30)
, last_name  varchar2(30)
, birthdate  date
)

things will be different: the function will be invalidated because of its dependency on the type, and in this case the contract between the function and the type has changed. So automatic recompilation will fail

select get_person
from   dual
/

select get_person
       *
ERROR at line 1:
ORA-06575: Package or function GET_PERSON is in an invalid state

and we have to manually alter the function to work with the new type definition:

create or replace
function  get_person
return    person_t
as
  l_person person_t := person_t(null,null,null);
begin
  l_person.first_name :='HELEN';
  l_person.last_name  :='TUDOR';
  return l_person;
end;

after making the change, the function can be recompiled and used again.

select get_person
from   dual
/

GET_PERSON(FIRST_NAME, LAST_NAME, BIRTHDATE)
--------------------------------------------------------------------------------
PERSON_T('HELEN', 'TUDOR', NULL)

the function can be enhanced to also return a value for birthdate, and this obviously does not impact the type:

create or replace
function  get_person
return    person_t
as
  l_person person_t := person_t(null,null,null);
begin
  l_person.first_name :='HELEN';
  l_person.last_name  :='TUDOR';
  l_person.birthdate  := sysdate - 365*43.3;
  return l_person;
end;

Retrieving the result of the modified function:

select get_person
from   dual
/

GET_PERSON(FIRST_NAME, LAST_NAME, BIRTHDATE)
--------------------------------------------------------------------------------
PERSON_T('HELEN', 'TUDOR', '27-MAY-66')

Let’s now create another type (ADT), one that has a dependency on the person_t type:

create or replace
type personal_relation_t as object
( relationship_type varchar2(20)
, start_date        date
, from_person       person_t
, with_person       person_t
)

This type can be used for example in queries or program code that works with relations between instances of person_t:

select personal_relation_t( 'FRIENDSHIP'
                          , sysdate - 200
                          , person_t('JOHN', 'DOE', to_date('12-FEB-1951', 'DD-MON-YYYY'))
                          , person_t('JANE', 'DOE', to_date('22-JAN-1958', 'DD-MON-YYYY'))
                          )
from   dual

PERSONAL_RELATION_T('FRIENDSHIP',SYSDATE-200,PERSON_T('JOHN','DOE',TO_DATE('12-F
--------------------------------------------------------------------------------
PERSONAL_RELATION_T('FRIENDSHIP', '14-FEB-09', PERSON_T('JOHN', 'DOE', '12-FEB-5
1'), PERSON_T('JANE', 'DOE', '22-JAN-58'))

And now, at least until 11gR2, we are now in a different situation with the person_t type because of the dependency from the personal_relation_t type. We are no longer free in our manipulation of person_t. Let’s see for example what happens when we try to alter the person_t type once more:

create or replace
type person_t as object
( first_name varchar2(30)
, last_name  varchar2(30)
, birthdate  date
, gender     varchar2(1)
)

by adding a gender attribute. The database will not allow this change:

create or replace
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

At this point, the only workaround is to drop all dependent types – in this example only personal_relation_t – and recreate both type, first person_t and then personal_relation_t.

In 11gR2 we can add FORCE to the create or replace statement for a type. From the documentation: "If type_name exists and has type dependents, but not table dependents, FORCE forces the statement to replace the type. (If type_name has table dependents, the statement fails with or without FORCE.)"  Force will make create or replace type do what I think it should have done all along: replace a type when there are no dependencies from nested tables/columns. It feels strange we need the FORCE keyword for this. But of course the most important thing is that we now have the ability to easily adapt our user defined types:

create or replace
type person_t force as object
( first_name varchar2(30)
, last_name  varchar2(30)
, birthdate  date
, gender     varchar2(1)
)
/

Type Created

Developers and DBAs may just build a slightly better relationship based on this new ability…

Resources

11gR2 SQL Language Reference Drop Type syntax – (already available in 10gR1)

11gR2 PL/SQL Language Reference – Create Type syntax (new in 11gR2)