Conversion from Oracle7 to Oracle9i

Conversion from Oracle7 to Oracle9i: An Example

You may soon be faced with the prospect of both migrating from Oracle7 to Oracle9i as well as converting from the Oracle7 relational table and data structure into the new object-based format for Oracle9i.

If you don't intend on taking advantage of the Oracle8, Oracle8i, and Oracle9i object extensions then this section isn't for you. If you are doing a straight  conversion, relational to relational, the conversion from Oracle7 to Oracle9i is simply a matter of migration. Even adding partitions to tables or indexes, or converting partition views to partition tables, is pretty straightforward. In fact, if you intend on using partitions, then you cannot use Oracle object types, such as nested tables, varrays, or REFs, as they are not supported for use in partitioned tables in Oracle8. They aren't supported until version 8i and 9i.

If, on the other hand, you are jumping into the deep end with Oracle8i or Oracle9i, and will be doing a full conversion into the new object-oriented structure, then by all means read on.

A Concrete Example

I?ll use a small sample application fragment to demonstrate how to map it from an Oracle7 relational table format into Oracle9i types and objects. The fragment is from a telecommunications application that provides for the tracking of clients, their assigned client numbers, phone numbers, contracts, and related data.

Note: For the purpose of this example, I have removed the storage and physical attributes from the application fragment, as they are not germane here.

The Oracle7 structure to be converted consists of 8 tables, 8 primary keys, and 11 foreign keys, with supporting indexes for the primary and foreign keys. The entity relational diagram for this structure is shown in Figure 5.1; the table definitions are given in Source 5.1. This structure shows that the client entity, which maps to the clients table, and the clients_info_number entity, which maps to the clients_info_numbers table, are the two main entities; all of the other entities are dependent upon these two controlling entities. This will evolve under Oracle9i into two objects, CLIENTS and CLIENTS_INFO_NUMBERS, which will absorb the other entities into a hierarchical object structure. This is shown in Source 5.2.

Figure 5.1 ERD for conversion example.

SOURCE 5.1 Oracle7 creation script for application fragment.

DROP TABLE clients CASCADE CONSTRAINTS; 

As you no doubt noticed,  many of the structures in Source 5.1 violate Third Normal Form; unfortunately, certain design restrictions forced this design; specifically, much of this was from a third-party application and therefore verboten to touch. If I could have designed the structure from the ground up, it would have been better normalized.

In order to convert this relational structure into an object-relational structure, we have to know the dependencies. In this case, we are working against the following business rules:

1.        Clients and clients_info_numbers can have independent existence; but usually, for every one client, there may be many or no clients_info_number records.

2.        A clients_info_number can be created without a parent client.

3.        The ftx_code and/or sub_code entities are dependent (i.e., they can't exist without the parent) on clients.

4.        The contract and/or circuit_id_info are dependent on clients_info_number.

5.        In some cases, the records in address are dependent on clients; sometimes they are dependent on clients_info_number.

6.        There can be no more than six franchise codes and no more than three ftx_codes per client.

7.        Up to three addresses can tie to a clients_info_number, but only one can tie to the client.

8.        A single clients_info_number can be tied to multiple contracts.

9.        A single clients_info_number can be tied to multiple circuit/phone numbers.

10.    A restrict_code indicator (up to 5) is used on a per-clients_info_number basis to restrict access to that number's information (this promulgates back up to any client information as well).

A client may have one client profile.

Under Oracle's implementation of object-oriented design in versions 8, 8i, and 9i object relationships are shown by use of REF statements. REF relationships are one-to-one. Since we can't make clients_info_number dependent on the client (see rule 1  in the previous list), we need the two main object structures, CLIENTS and CLIENTS_INFO_NUMBERS. CLIENTS and CLIENTS_INFO_NUMBERS will relate by a REF from CLIENTS_INFO_NUMBERS to CLIENTS. All of the other dependent entities will roll up into one of the following: a type, a nested table, or a varray internal object.

For dependent entities whose behavior is limited to a fixed number of occurrences per parent record, Oracle suggests the use of varrays that are stored inline with the parent records in RAW format. However, tests indicates this wastes space, so a nested table may be more efficient. For multiple relations, where the ultimate number is unknown or is extremely high, or the size of the resulting RAW would be too long (i.e., anytime in early releases), I suggest using a nested table. For related one-to-one data, such as the RESTRICT_CODE data and the client profile data, I suggest using a type specification. 

These are some rules for using types:

1.        A varray or nested table cannot contain a varray or nested table as an attribute.

2.        When using nested tables, you must specify a store table in which to store their records.

3.        Store tables inherit the physical attributes of their parent table.

4.        Default values cannot be specified for varrays.

5.        Constraints (including NOT NULL) cannot be used in type definitions (they must be specified using an ALTER TABLE command).

6.        A table column specified as a varray cannot be indexed.

7.        A table using varrays or nested tables cannot be partitioned.

8.        Varrays cannot be directly compared in SQL.

9.        Incomplete types (forward typing) are allowed, but an incomplete type cannot be used in a CREATE TABLE command until it is complete.

10.    The scalar parts of a type can be indexed directly in the parent table object.

11.    Varray and nested table subattributes cannot be indexed directly on a parent table object.

Nested table store table attributes can be indexed.

Let's take a look at how this maps into the CREATE TYPE, varray, and NESTED TABLEs of Oracle9i. Look at Source 5.2, the code to implement the structure as remapped to Oracle8i. A simplified UML diagram of the new structure is shown in Figure 5.2; the symbols used are shown in Figure 5.3.

SOURCE 5.2 Oracle9i code to implement application fragment.

rem
rem First drop then create the types, varrays, and nested table
rem definitions.
rem Order is important; you cannot delete a type with dependent
rem types, varrays, or nested tables.
rem
DROP TABLE clients_info_numbersv9i;
DROP TYPE clients_info_t force;
DROP TABLE clientsv9i;
DROP TYPE client_t force;
DROP TYPE sub_v force;
DROP TYPE ftx_v force;
DROP TYPE ceo_t force;
DROP TYPE restrict_code_t force;
DROP TYPE address_list force;
DROP TYPE address_t force;
DROP TYPE contract_list force;
DROP TYPE contract_t force;
DROP TYPE circuit_list force;
DROP TYPE circuit_t force;
rem
rem There can be multiple contracts so let's
rem make it a nested table
rem
CREATE TYPE circuit_t AUTHID DEFINER AS OBJECT (
     connect_type                    CHAR(1),
     connected_number                VARCHAR2(36)
);
/ 

CREATE OR REPLACE TYPE circuit_list AS TABLE OF circuit_t;
/
rem
rem There can be multiple contracts; let's make it a
rem nested table
rem
CREATE OR REPLACE TYPE contract_t AUTHID DEFINER AS OBJECT (
     contract_number          CHAR(15)
);
/
CREATE OR REPLACE TYPE contract_list AS TABLE OF contract_t;
/
rem
rem There was a fixed number of franchise codes allowed and it was small
rem so use a VARRAY
rem
CREATE OR REPLACE TYPE sub_t AUTHID DEFINER AS OBJECT (
     sub_code          VARCHAR2(8)
);
/
rem
rem sub_v is a VARRAY of 10 elements
rem
CREATE OR REPLACE TYPE sub_v AS VARRAY(10) OF sub_t;
/
rem
rem There is a fixed number of SIC codes and it is small
rem so use a VARRAY
rem
CREATE OR REPLACE TYPE ftx_t AUTHID DEFINER AS OBJECT (
     ftx_code                          CHAR(8) ,
     ftx_code_desc                     VARCHAR2(32),
     primary_ftx_code_ind              CHAR(1)
);
/
rem
rem ftx_v is a VARRAY of 6 elements
rem
CREATE OR REPLACE TYPE ftx_v AS VARRAY(6) OF ftx_t;
/
rem

rem The LOOKUP information is a one-to-one type
rem data set so use a type definition directly into the object
rem
CREATE OR REPLACE TYPE lookup_t AUTHID DEFINER AS OBJECT(
       lookup_no                         VARCHAR2(9) ,
       lookup_parent                     VARCHAR2(9),
       lookup_str_adrs                   VARCHAR2(25),
       lookup_city                       VARCHAR2(20),
       lookup_state                      VARCHAR2(2),
       lookup_zip                        VARCHAR2(5),
       lookup_zip_ext                    VARCHAR2(4),
       lookup_type                       CHAR(2),
       lookup_parent_flag                CHAR(1)
);
/
rem
rem The address information is fairly long, so even though
rem it is a fixed number of values, let's put it in a nested table.
rem This data is from a legacy system; addresses can have from
rem 5 to 15 lines of data.
rem
CREATE OR REPLACE TYPE address_t AUTHID DEFINER AS OBJECT (
      addrtype          INTEGER ,
      address1          VARCHAR2(80),
      address2          VARCHAR2(80),
      address3          VARCHAR2(80),
      address4          VARCHAR2(80),
      address5          VARCHAR2(80),
      address6          VARCHAR2(80),
      address7          VARCHAR2(80),
      address8          VARCHAR2(80),
      address9          VARCHAR2(80),
     address10          VARCHAR2(80),
     address11          VARCHAR2(80),
     address12          VARCHAR2(80),
     address13          VARCHAR2(80),
     address14          VARCHAR2(80),
     address15          VARCHAR2(80)
);
/
rem
rem address_list is a nested table definition
rem
CREATE OR REPLACE TYPE address_list AS TABLE OF address_t;
/
rem
rem The restrict_code data is a one-to-one type relation
rem so let's use a type definition directly into the object.
rem
CREATE OR REPLACE TYPE restrict_code_t AUTHID DEFINER AS OBJECT(
       restrict_code_1               VARCHAR2(14),
       restrict_code_2               VARCHAR2(14),
       restrict_code_3               VARCHAR2(14),
       restrict_code_4               VARCHAR2(14),
       restrict_code_5               VARCHAR2(14)
);
/
rem
rem The CEO data is a one-to-one relationship, so just use
rem a type definition directly into the object.
rem
CREATE OR REPLACE TYPE ceo_t AUTHID DEFINER AS OBJECT (
     ceo_first_name         VARCHAR2(13),
     ceo_last_name          VARCHAR2(15),
     ceo_middle_initial     VARCHAR2(1),
     ceo_suffix             VARCHAR2(3),
     ceo_prefix             VARCHAR2(10),
     ceo_title              VARCHAR2(30)
);
/
rem
rem The client table is the master in this set. Now that
rem the dependent types, VARRAYs, nested tables, and
rem REF table have been created, go ahead and create it.
rem
CREATE OR REPLACE TYPE client_t AUTHID DEFINER AS OBJECT (
     clients_id              INTEGER ,
     addresses               address_list,
     customer_name           VARCHAR2(35) ,
     active_flag             VARCHAR2(1),
     fax                     VARCHAR2(20),
     lookups                 lookup_t ,
     phone                   VARCHAR2(20),
     corporate_name          VARCHAR2(30),
     creation_ts             DATE,
     creation_sy_user        NUMBER(38),
     spp_rating              CHAR(2),
     rating_date             DATE,
     competitor_loss         INTEGER,
     last_contact_ts         DATE,
     delete_status           CHAR(1),
     name_soundex            CHAR(4),
     sales_volume            VARCHAR2(15),
     sales_volume_code       CHAR(1),
     total_employees         VARCHAR2(9),
     line_of_bus             VARCHAR2(19),
     pct_growth_sales        VARCHAR2(4),
     territory_covered       CHAR(1),
     mrc                     VARCHAR2(4),
     ceo                     ceo_t,
     sub_indctr              CHAR(1),
     ftx_codes               ftx_v,
     sub_codes               sub_v,
     MEMBER PROCEDURE do_soundex(id IN integer, nor_val IN varchar2)
);
/
rem
rem Now create the object clients, which contain
rem nested tables, types, and normal attributes
rem
CREATE TABLE clientsV9i OF client_t
OIDINDEX oid_clientsV9i (TABLESPACE APPL_INDEX)
NESTED TABLE addresses STORE AS addressesv9i
       PCTFREE 10
       PCTUSED 80
       INITRANS 5
       MAXTRANS 255
       TABLESPACE APPL_DATA
       STORAGE (
              INITIAL 20m
              NEXT 10m
              MINEXTENTS 1
              MAXEXTENTS 10
              PCTINCREASE 0
       )
;
/
ALTER TABLE clientsV9i ADD
       CONSTRAINT PK_clientsv9i
              PRIMARY KEY (clients_id)
       USING INDEX
              PCTFREE 20
              INITRANS 5
              MAXTRANS 255
              TABLESPACE APPL_INDEX
              STORAGE (
                     INITIAL 10m
                     NEXT 10m
                     MINEXTENTS 1
                     MAXEXTENTS 121
                     PCTINCREASE 0
                     FREELISTS 5
              )
;
/
ALTER TABLE clientsV8i MODIFY
     customer_name NOT NULL;/
CREATE OR REPLACE TYPE BODY client_t IS
MEMBER PROCEDURE do_soundex(id IN integer, nor_val IN varchar2) IS
sx_val integer;
begin
     sx_val:=soundex(nor_val);
     update clientsv9i set name_soundex=sx_val where clients_id=id;
end;
END;
/
rem
rem from clientsv9i. We will REF client_t and CLIENTSV9i
rem
CREATE OR REPLACE TYPE clients_info_t AUTHID DEFINER AS OBJECT (
     clients_info_nmbrs_id      INTEGER,
     clients_id_r               REF client_t,
     listed_name                VARCHAR2(100),
     earning_number             CHAR(13),
     service_class              VARCHAR2(5),
     restrict_code              restrict_code_t,
     no_of_lines                NUMBER(4),
     disconnect_date            DATE,
     disconnect_reason          CHAR(2),
     billing_name               VARCHAR2(40),
     phone                      VARCHAR2(10),
     btn                        CHAR(13),
     old_clients_number         CHAR(13),
     service_address            VARCHAR2(100),
     con_ctrl_number            CHAR(15),
     term_agreement             CHAR(13),
     shared_tenant_svcs         VARCHAR2(10),
     installation_date          DATE,
     contracts                  contract_list,
     circuits                   circuit_list,
MEMBER PROCEDURE get_client_id_ref
(client_id IN integer, earning_id IN integer)
);
/
rem
rem clients_info_numbers is a table definition
rem
CREATE TABLE  clients_info_numbersV9i OF clients_info_t
     (clients_id_r WITH ROWID
     SCOPE IS tele_dba.clientsv9i)
     OIDINDEX oid_clients_info_nmbrsV9i (TABLESPACE APPL_INDEX)
     NESTED TABLE contracts STORE AS contractsV9i
     NESTED TABLE circuits STORE AS circuitsV9i
     PCTFREE 10
       PCTUSED 80
       INITRANS 5
       MAXTRANS 255
       TABLESPACE APPL_DATA
       STORAGE (
              INITIAL 20m
              NEXT 10m
              MINEXTENTS 1
              MAXEXTENTS UNLIMITED
              PCTINCREASE 0
       )
;
ALTER TABLE clients_info_numbersV9i ADD
       CONSTRAINT PK_clients_info_numbersV9i
              PRIMARY KEY (clients_info_nmbrs_id)
       USING INDEX
              PCTFREE 20
              INITRANS 5
              MAXTRANS 255
              TABLESPACE APPL_INDEX
              STORAGE (
                     INITIAL 10m
                     NEXT 10m
                     MINEXTENTS 1
                     MAXEXTENTS UNLIMITED
                     PCTINCREASE 0
                     FREELISTS 5
              )
;
CREATE OR REPLACE TYPE BODY clients_info_t AS
MEMBER PROCEDURE get_client_id_ref
(client_id IN integer, earning_id IN integer)IS
begin
        update CLIENTS_INFO_NUMBERSV9i z
   set z.clients_id_r =
   (SELECT REF(x) FROM clientsv9i x
        WHERE x.clients_id=client_id)
   WHERE z.clients_info_nmbrs_id=earning_id;
end;
END;
/

Figure 5.2 Simplified UML diagram of converted Oracle7 application fragment.

Figure 5.3 Symbol definition for simplified UML diagram.

Notice in the code in Source 5.2 the use of the following coding conventions:

* All TYPES end in ?_t.?

* All varrays end in ?_v? (I use ?_vw? for views).

* All NESTED TABLES end in ?_list.?

* When used in a DDL statement, native datatypes are capitalized, while user-defined types are lowercased.

* The entities are singular, while the tables or objects that they become are plural or are a plural or neutral form.

* All REF columns end in ?_r.?

* All primary keys have the prefix ?PK_? followed by the table name.

* All foreign keys have the prefix ?FK_? followed by the table name and arbitrary integer.

* All lookup keys have the prefix ?LU_? followed by the table name and arbitrary integer.

* All unique value keys have the prefix ?UK_? followed by the table name and an arbitrary integer.

* All object ID indexes (OID) have the prefix ?OID_? followed by the table name.

Also notice that each section is remarked (in the new code) to tell what is going on and why. These are good practices and should be emulated (I don't say this is the best way or the only way; I do recommend that you develop a methodology that makes sense to your environment).

I want to point out here that, in version 8.1.3, the following type creation in the Source 5.2 didn't work, and resulted in an ORA-03113 and forced disconnection. It did, however, work in 8.0.5 and 8.1.5.

CREATE OR REPLACE TYPE client_t as object (

     clients_id              INTEGER ,
     addresses               address_list,
     customer_name           VARCHAR2(35) ,
     active_flag             VARCHAR2(1),
     fax                     VARCHAR2(20),
     lookups                 lookup_t ,
     phone                   VARCHAR2(20),
     corporate_name          VARCHAR2(30),
     creation_ts             DATE,
     creation_sy_user        NUMBER(38),
     spp_rating              CHAR(2),
     rating_date             DATE,
     competitor_loss         INTEGER,
     last_contact_ts         DATE,
     delete_status           CHAR(1),
     name_soundex            CHAR(4),
     sales_volume            VARCHAR2(15),
     sales_volume_code       CHAR(1),
     total_employees         VARCHAR2(9),
     line_of_bus             VARCHAR2(19),
     pct_growth_sales        VARCHAR2(4),
     territory_covered       CHAR(1),
     mrc                     VARCHAR2(4),
     ceo                     ceo_t,
     sub_indctr              CHAR(1),
     ftx_codes               ftx_v,
     sub_codes               sub_v,
     MEMBER PROCEDURE do_soundex(id IN integer, nor_val IN varchar2)
);

This next type creation did work. Notice that the record length has been shortened by removing several VARCHAR2 columns. Other than that it is identical, and the changes allowed the rest of the script to complete:

CREATE OR REPLACE TYPE client_t as object (
     clients_id              INTEGER ,
     addresses               address_list,
     customer_name           VARCHAR2(35) ,
     active_flag             VARCHAR2(1),
     lookups                 lookup_t ,
     creation_ts             DATE,
     creation_sy_user        NUMBER,
     spp_rating              CHAR(2),
     rating_date             DATE,
     competitor_loss         INTEGER,
     last_contact_ts         DATE,
     delete_status           CHAR(1),
     name_soundex            CHAR(4),
     sales_volume            VARCHAR2(15),
     sales_volume_code       CHAR(1),
     territory_covered       CHAR(1),
     mrc                     VARCHAR2(4),
     ceo                     ceo_t,
     sub_indctr              CHAR(1),
     ftx_codes               ftx_v,
     sub_codes               sub_v,
     MEMBER PROCEDURE do_soundex(id IN integer, nor_val IN varchar2)
);
/

This seems to indicate a row length limit when using types in 8i, version 8.1.3, which wasn't present in 8.0.5 and isn't present in 8.1.5, so if you use an older release of 8i, be careful. It is not present in 9i.

If I had included the storage and physical attributes for the standard relational Oracle7 code, this fragment would have been 12 pages long. The object relational Oracle9i code (with storage clauses) is only five pages. The Oracle7 DDL must have the primary tables created first, then the related tables (or all tables, then the constraints). The Oracle9i code must have all types, varrays, and nested tables, as well as related tables, before the primary tables can be defined. If methods used in the type bodies are dependent on specific existing tables, then those tables must be created before the type bodies. This indicates that the Oracle8, Oracle8i, and Oracle9i system will require more analysis on the front end to build properly. If this analysis is not done properly, the rebuilding will be more complex than with an Oracle7 database structure, at least in versions prior to Oracle9i. In Oracle9i, the ability to cascade TYPE changes will make fixing oversights and mistakes easier.

Notice that the number of indexes dropped from 19 to 4. This is because, as tables are made into nested tables, Oracle in its object-oriented paradigm adds another column (SETID$) that is in the structure of their store tables. This SETID$ value is added to the applicable indexes to establish the proper relations. This is done under the covers, and DBAs need not concern themselves with it. The store tables inherit the physical attributes of their master table. The store tables can be modified just as regular tables can; thus, if required, you can add performance-enhancing indexes, as well as alter storage parameters.

Again, the order is critical. Notice that the type bodies come after the table created with the types (for client_t and clients_info_t). This is because the methods included in the bodies are dependent on the clients and clients_info_numbers tables to be valid. Also note that the clients table is created prior to the clients_info_numbers table. This is required because the column clients_id_r references the clients table. (Note that a REF can only refer to one entry in a referenced object; therefore, references always go from the dependent table to the controlling table, from the many side of the relation to the one side.)

In the table definition for the clients_info_numbers table, examine the first couple of lines that follow the CREATE line:

clients_id_r WITH ROWID
SCOPE IS tele_dba.clientsv9i

These commands ?finish? the REF command that was started in the type declaration. Because a type is generic in nature, you cannot limit the scope of a REF value inside a type declaration. Instead, you must restrict the value at point of use, in this case, the table creation. These commands allow the rowid pseudocolumn to be stored with the OID from the REFed object table. This storing of the rowid and OID speeds any UNREF activities. The SCOPE command restricts all REFs from this column to the specified table; this also reduces the space requirements for the REF column value and speeds access.

The OIDINDEX clause in both CREATE TABLE  commands creates an index on the object identifier that can then be used to speed REF type queries against the tables. In this situation, the clients_info_numbersV9i object table will be REFing the clientsv9i object table, so placing the clientsv9i OIDs into an index is a performance-enhancing idea. The OIDINDEX on clients_info_numbersV9i is just good form.

Conversion Summary

To summarize the conversion example here are a few guidelines:

1.        Attribute sets that are one-to-one with the main object should be placed in a TYPE definition and used directly.

2.        Attribute sets that have a low, fixed number of occurrences should be placed into varrays. (But note, this may not be true if constraints or direct comparisons are required.)

3.        Attribute sets with many occurrences or that require constraints and value-to-value comparison should be placed in nested tables.

4.        If a type is to be used in a REFable object, the object must be created using the AS OBJECT clause.

5.        REF clauses in a TYPE declaration must be finished via an ALTER TABLE command on the final object table if scoping or rowid storage is required.

6.        Use of WITH ROWID and OIDINDEX clauses should be encouraged to speed access and, in some cases, to reduce storage requirements.

7.        Analysis of dependencies is critical to success.

8.        In some versions of 8i, be careful of row length; there seems to be an undocumented length limitation.

Oracle8, Oracle8i, and Oracle9i will require a great deal more front-end analysis in order to prevent recoding. To pull together the example in this section, I had to use the DBA, Application Developer, Server Concepts, PL/SQL, and SQL Reference Manuals (Oracle9i beta copies and OracleTechnet, supplied online, 9.0.1 versions; http://technet.oracle.com/). Even with references, it initially took two days of testing for the Oracle8 rewrite and an additional day with Oracle8i and Oracle9i to get clean builds of the code in Source 5.2 (hence the drop commands at the top of the script). Oracle9i is a new view of the world, and you will have to change your perception of how the database works in order to fully utilize its provided features.