Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

Exclusive offer: get 50% off this eBook here
Oracle SQL Developer 2.1

Oracle SQL Developer 2.1 — Save 50%

Design and Develop Databases using Oracle SQL Developer and its feature-rich, powerful user-extensible interface with this book and eBook

$32.99    $16.50
by Dr.Jay Krishnaswamy | September 2007 |

In this article, Jayaram Krishnaswamy shows how to migrate an MS Access database to an Oracle 10G XE Server, describing all the steps involved in the migration process.

Introduction

Business needs often necessitate data migration from a smaller, less secure database to a higher end, faster database server with a more reliable availability. A typical scenario is the migration of data from a desktop sized database such as MS Access or Fox Pro to any other higher end database servers such as MS SQL Server, Oracle, DB2 or SQL Anywhere Server. Most of the database vendors provide tools to migrate from third party to their own database servers. In his three previous articles, the author has described the built-in tools to migrate from MS Access to SQL 2000 Server, SQL Anywhere Server, and from Oracle 10G XE to SQL Anywhere server.

 

In an earlier article on this site, the author showed how you may connect to an MS Access 2003 database and execute SQL statements using the Oracle SQL Developer 1.2 tool. In this tutorial the author shows you how to migrate an MS Access database to an Oracle 10G XE Server delineating all the steps involved in the migration process.

Oracle SQL Developer 1.2 with this latest version is sometimes called the Migration version as it supports migrating data from three vendors (MySQL, SQL Server and MS Access) to an Oracle database. In fact, it has been designed to migrate from more than one version of MS Access. This feature was not available in the version 1.1 of this tool.

Overview of this Tutorial

Like in the earlier article, a simple MS Access 2003 database file will be created with just one table, a query and a linked table. This database file, about 292 KB, will be migrated to Oracle 10G XE database.

Oracle 10G XE, by design, can have just one database on a computer. However, you can have separate applications by having different user schemas. Oracle 10G XE comes bundled with a sample database schema and data which can be accessed by using the credentials, username hr with a password hr. For the purposes of this example a new user will be created and his authentication will be used for creating necessary migration related schemas to be stored in a repository. This will become clear as you follow the various details and the steps.

Once the ‘Repository’ is created then you can begin by capturing the metadata of the source followed by converting the captured source information into Oracle specific model where a mapping between the source data and the Oracle will be accomplished. After this process, you generate the data definition language script which will create the Oracle objects such as tables, views, etc. In the final step these tables will be populated by transferring the data from the source to Oracle 10G XE.

MS Access 2003 Source

An empty MS Access database file TestMigration.mdb is created in the default directory, My Documents. An Employees table will be imported, an Orders table will be linked and a TestQuery based on selecting a few columns of Employees table will be created. The Employees table and the Orders table may be found in the Northwind Database that ships with most of the MS Access versions.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

Creating a New User in Oracle 10G XE

As described in the overview, the MS Access Database will be migrated to a User schema in Oracle 10G XE, but this requires reating this schema.
Only a user with DBA privileges can create a new user. Open the Homepage of the Oracle 10G XE Server. Login with the credentials you supplied while installing the software where the user is system and the password is what you chose at that time, as shown in the next figure.

 

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

This gives you access to several of the tools that you can use to administer as well as work with database objects. Click on the icon for Administration and follow the drop-downs till you get to the menu item, Create User, as shown in the next figure.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

Create a new user MigrateAccess with some password that you choose and confirm. Keep the account status unlocked. This uses the default tablespace called USERS. The default user privilege does not include the DBA role but for this example, the DBA is also included by placing a check mark in this selection. Also several other system wide privileges are also granted. Please follow steps described in the earlier article for the details. The next figure shows all the details filled in.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

After this when you click the Create button you will have created the user, MigrateAccess. When you click the button Create, you will notice that the ‘bread crumb’ will change to Manage Database Users. You will notice that the new user MigrateAccess has been added to the list of users, as shown in the next figure. As no expiry was set for this user in the previous screen, you can notice that there is no expiry shown in the following screen.
Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2
Now if you logout (remember you logged in as SYSTEM) and login with the new credentials, MigrateAccess/[chosen password] you can access all the tools on the database. Of course, all the objects (tables, views, etc) will be empty.

Creating the Repository to Store Schemas

Migration using this tool requires an Oracle database schema to store the Meta data it collects about the source. You will create a connection from the Oracle SQL Developer to the Oracle 10 XE, in which, you just finished creating a new user schema. This user’s schema is where the repository contents will be stored.

Making a connection to the Oracle

Right click on the Connections node, and from the drop-down menu select New Connection. This brings up the New / Select Database Connection (this has been described in the earlier referenced article) window. It comes up with the default connection to an Oracle database. It even recognizes the local Oracle 10G XE, capturing all its details as shown. You need to provide a Connection Name, a Username and a Password. The connection name is your choice (herein called conMigrate) and the user name and password is the same that was used while creating the new user MigrateAccess. When you click on the button ‘Test’, a (success) status message will be posted to this form above the Help button, as shown in the next figure after a little while, preceded by a little progress window.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

Now click on the OK button on the New / Select Database Connection window.

This adds the conMigrate connection to the list of Connections as shown in the next figure. Notice that objects are all empty as we discussed earlier.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

Create Repository

Click on the main menu item Migrate. From the drop-down, click on Repository Management –> Create Repository as shown in the next figure.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

This brings up the Create Repository window showing the connection conMigrate as shown in the next figure. You may connect or disconnect this from the tool as long as the authentication information is available.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

Now click on the Create button. This brings up the Installing Repository window which reports the various objects installed and finally shows a message “Repository Built Successfully” as shown in the next figure.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

Click on the Close button on this window.

Now login to the Oracle 10G XE with the credentials for the user MigrateAccess, and click on the object browser. Now you see all the Tables, Views, etc in the repository as shown.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2
You will notice that either two more windows, named captured and converted models appear below the Connections node in Oracle SQL Developer, or if they are not found in the Connections node, you may find in the submenu of the main menu, View.

The next figure shows the submenus of the View menu.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

Connect to the Source Database

Right click on the connection node and establish a new connection so that you can connect to the source database, conTestMigration as shown in the next figure. When you click the Test button you will see a message that gets posted to the screen indicating the connection was a success. Click on the Connect button. This adds the conTestMigrate connection to the list of Connections in the navigator window.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

Oracle SQL Developer 2.1 Design and Develop Databases using Oracle SQL Developer and its feature-rich, powerful user-extensible interface with this book and eBook
Published: December 2009
eBook Price: $32.99
Book Price: $54.99
See more
Select your format and quantity:

Capturing Source Meta Data

Right click on the conTestMigrate connection and from the drop-down menu click on Capture Microsoft Access submenu as shown in the next figure.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

This opens up the Capturing Database window/progress bar which also brings up the MS Access database. After a while the message Capture Done is posted as shown in the Capturing Database window.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

The captured objects are available in the captured objects window which can be accessed from the View menu as shown. Although the connection used was conTestMigration (to connect to the TestMigration database) it is not very clear, why yet another database (Charts, Charts.mdb does exist in My Documents) has also appeared. You can expand the nodes and look at all the tables (Their contents as well), views etc. Additionally by right clicking each of the objects you can rename, or update the data type. Here, no alterations were made.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

Converting Captured Model to Oracle Model

Now right click on the TestMigration node in the Captured Models window in the navigator and from the drop-down click on Convert to Oracle Model. This opens up a Set Data Map window showing source data types together with the Oracle data types [22 data types]. By placing a check mark you may view only those data types that are relevant to the source data as shown in the next figure.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

Clicking on the Add New Rule button you may be able to edit the data type to a different acceptable one, as shown in the next figure. Here, the defaults are used.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

Now click on the Apply button.

This brings up the Converting Database window which doubles as a progress bar as well. When the process is complete, you get a message Convert Done posted to the page as shown.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

Click on the Close button. Now you can see the converted objects by accessing this window in the View menu as before. The expanded view of the table in the converted scheme is shown in the next figure (see Figure 16 for the source).

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

Generating the Data Definition Language for New Objects in Oracle

In the converted model you can right click on an object, or a group of objects (such as a table), or the whole model and from the drop-down menu. Click on Generate as shown in the next figure. In the figure shown, the DDL will be generated for all the objects in the TestMigration converted objects.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

Clicking on Generate submenu item brings up the Generating Oracle SQL window which also shows the progress in the SQL generation as shown in the next figure. When the generation of SQL is complete a Generation Done message will be posted to this window as shown.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

Click on the Close button on this window. The script generated by the above now shows up in the Enter SQL Statement window in the SQL Worksheet as shown.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

The full text of this script is shown in the next paragraph.

Listing 1

SET SCAN OFF;
CREATE USER TestMigration IDENTIFIED BY TestMigration
DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
GRANT CREATE SESSION, RESOURCE, CREATE VIEW TO
TestMigration;connect TestMigration/TestMigration;

CREATE SEQUENCE  Employees_EmployeeID_SEQ
 MINVALUE 0 MAXVALUE 999999999999999999999999
INCREMENT BY 1  NOCYCLE ;
CREATE TABLE Employees (
  EmployeeID NUMBER (11,0) NOT NULL,
  LastName VARCHAR2 (20) NOT NULL,
  FirstName VARCHAR2 (10) NOT NULL,
  Title VARCHAR2 (30),
  TitleOfCourtesy VARCHAR2 (25),
  BirthDate DATE,
  HireDate DATE,
  Address VARCHAR2 (60),
  City VARCHAR2 (15),
  Region VARCHAR2 (15),
  PostalCode VARCHAR2 (10),
  Country VARCHAR2 (15),
  HomePhone VARCHAR2 (24),
  Extension VARCHAR2 (4),
  Photo BLOB,
  Notes CLOB,
  ReportsTo NUMBER (11,0)
);

CREATE INDEX LastName ON Employees
(
  LastName
)
;
CREATE INDEX PostalCode ON Employees
(
  PostalCode
)
;

connect TestMigration/TestMigration;

CREATE OR REPLACE VIEW TestQuery AS

SELECT Employees.EmployeeID,
       Employees.LastName,
       Employees.BirthDate,
       Employees.Address,
       Employees.Country,
       Employees.HomePhone
  FROM Employees;



connect TestMigration/TestMigration;

CREATE OR REPLACE TRIGGER Employees_EmployeeID_TRG
BEFORE INSERT OR UPDATE ON Employees
FOR EACH ROW
BEGIN
  if inserting and :new.EmployeeID is NULL then
  SELECT Employees_EmployeeID_SEQ.nextval into:
new.EmployeeID FROM DUAL;
  end if;
END;

/

ALTER VIEW TestQuery COMPILE;

Essentially a new schema TestMigrate, will be created in Oracle 10G XE when this script is run whose authentication is (Username/Password) TestMigrate/TestMigrate. The script when executed will create all the objects.

Creating New User and Schema Objects

Now when you run this script, you will have created all the objects. You can click on the Run Script (F5), as shown, to run the generate script.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

This brings up the Select Connection window as shown where you need to specify a connection. The oracle connection conMigrate is chosen as shown.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

When you choose the conMigrate, some of the information gets displayed in the Select Connection window as shown.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

Now click on the OK button. After the script is successfully run, the Script Output window shows the result of running the script as shown.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

Reviewing Meta Data in Oracle 10G XE

Now you can login to Oracle 10G XE with the credentials [username/password] TestMigration/TestMigration and browse the objects clicking on the Object browser. You will be seeing all the objects created. The meta data is there but the objects are empty of data.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

Copying the Data

In the previous step you have all the meta data, but the data in the source needs to be transferred to those objects. Click on the Main menu Migrate and from the drop-down choose Migrate Data as shown in the next figure.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2
This brings up the Data Move Details window. Click on the drop-down handles appropriately and choose the source and target connections as shown. You may have to choose the Converted Model as well although there is just one in this example.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

Click on the OK button on the Data Move Details window after making all the selections.

This brings up the Moving Data to Oracle window which also displays progress. The final display when the tables are completed, is as shown in the figure.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

Close this window by clicking the Close button.

Reviewing Migrated Data

Now login to the Oracle 10G XE once again, to the user schema TestMigration with username/password pair to match TestMigration/TestMigration, and click on the object browser as before. Now you see the data in the Employee table as shown. Data movement during movement of data to Oracle was sluggish and the button has to be clicked a couple of times before the Completed Tables showed up.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

Similarly the view TESTQUERY is created from the MS Access query, TestQuery in the source as shown.

Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2

Similarly, you can view indexes, triggers etc. The linked table, of course, does not migrate.

Conclusion

Oracle SQL Developer 1.2 is an excellent tool for migrating MS Access 2003 database to Oracle 10G XE. The steps are clear and well laid out. The data mapping between the source and the target is very clear and can be modified. Also as described, the capturing process showed objects other objects in addition to the intended ones (see Figure 15). Not sure if this is a bug. Copying the data step had to be clicked multiple times before the Completed Tables showed up.

Oracle SQL Developer 2.1 Design and Develop Databases using Oracle SQL Developer and its feature-rich, powerful user-extensible interface with this book and eBook
Published: December 2009
eBook Price: $32.99
Book Price: $54.99
See more
Select your format and quantity:

About the Author :


Dr. Jay Krishnaswamy a graduate of the Indian Institute of Science, Bangalore writes on database and web development related topics to several computer programming related web sites. He is an active participant in several forums and discussion groups. Before working in the IT industry as a Microsoft Certified Trainer and a Siebel Certified consultant he taught at several institutes of technology and universities in India, Australia, Brazil and the USA. Links to his articles may be found at his web site or the blog. He lives in Plainsboro, NJ, USA and may be reached at jkrishnaswamy@comcast.net

Books From Packt

 

Oracle Modernization Solutions
Oracle Modernization Solutions

Oracle Web Services Manager
Oracle Web Services Manager

Oracle SOA Suite Developer's Guide
Oracle SOA Suite Developer's Guide

Processing XML documents with Oracle JDeveloper 11g
Processing XML documents with Oracle JDeveloper 11g

JDBC 4.0 and Oracle JDeveloper for J2EE Development
JDBC 4.0 and Oracle JDeveloper for J2EE Development

SOA Cookbook
SOA Cookbook

Mastering Oracle Scheduler in Oracle 11g Databases
Mastering Oracle Scheduler in Oracle 11g Databases

ODP.NET Developer’s Guide: Oracle Database 10g Development with Visual Studio 2005 and the Oracle Data Provider for .NET
ODP.NET Developer’s Guide: Oracle Database 10g Development with Visual Studio 2005 and the Oracle Data Provider for .NET

 


Your rating: None Average: 5 (1 vote)

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
5
c
x
y
6
2
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software