Ms Access How to Read Data From Oracle Database

Migrating a Microsoft Access Database to Oracle Database 11g

This tutorial shows you how to migrate a Microsoft Access database to Oracle Database 111000 using Oracle SQL Developer.

Approximately 30 minutes

This tutorial covers the following topics:

	Overview
	Prerequisites
	Creating the mwrep User
	Creating the Migration Repository
	Capturing the Microsoft Access Exported XML
	Checking Conversion Preferences
	Converting to the Oracle Model
	Generating and Executing the Script to Create the Oracle Database Objects
	Checking Offline Data Move Preferences
	Assay and Estimation
	Migrating the Data
	Testing and Deployment
	Summary

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be tiresome depending on your Cyberspace connection.)

Note: Alternatively, you tin can place the cursor over an individual icon in the following steps to load and view merely the screenshot associated with that stride. You can hibernate an private screenshot past clicking information technology.

What Is SQL Developer?

Oracle SQL Programmer is a free graphical tool that enhances productivity and simplifies database development tasks. Using Oracle SQL Programmer, you can browse database objects, run SQL statements, edit and debug PL/SQL statements and run reports, whether provided or created.

Microsoft Admission Migration Overview

Using Oracle SQL Developer Migration Workbench, yous can apace drift your Microsoft Access database to Oracle.

There are four main steps in the database migration procedure:

Capture the Source Database	The commencement step is to capture a "snapshot" of the Microsoft Access database. This tin be washed in two means. Online Capture: This requires creating a connection in SQL Developer to a Microsoft Admission database. Using JDBC, the Microsoft Admission database metadata can exist accessed and the Capture Model created. Offline Capture: This involves using the Exporter to extract the metadata out of the Microsoft Admission database to files. These files tin can and then be "captured" by SQL Programmer. Due to licensing reasons, Microsoft Access is not installed for the tutorial. The online approach is recommended, only for the purposes of this tutorial the second method volition be performed. The Northwind.xml file generated by the Exported tool contains the database schema information for the Microsoft Admission Northwind Traders database. Oracle SQL Developer Migration Workbench uses this file equally the ground for edifice a representation of the structure of the source Microsoft Access database. This structure is called the Captured Model.
Convert the Captured Database	Oracle SQL Developer Migration Workbench uses the Captured Model to convert the captured objects to Oracle-format objects, edifice up a representation of the construction of the destination database. This structure is called the Converted Model.
Generate the Oracle Database	Oracle SQL Programmer Migration Workbench generates DDL statements to create the new Oracle database, based upon the Converted Model objects. Running the DDL statements will result in the creation of the objects in the Oracle database.
Migrate the Information	The last step in the process is to drift the data. You tin can practise this in one of 2 ways. Online Data Move: You can create a connection from within Oracle SQL Programmer to your Microsoft Access source database and drift the data. Offline Data Move: Y'all can export the data from Microsoft Access using the Exporter. SQL Developer will create a series of sqlldr files that y'all can run from a batch file to load the data into Oracle. The 2d method is what you will perform in this tutorial.

In this tutorial, the required scripts for the offline migration have already been generated and modified. If yous do non take time to perform this tutorial, yous can too view the offline method, click here.

To view the steps for the online method, click here.

Back to Topic Listing

Earlier you perform this tutorial, you should:

i.	Install the Oracle Database 10g or later on, or Oracle Database XE
2.	Download and unzip Oracle SQL Programmer here.
3.	Download and unzip the msaccessmigration.zip file into your working directory (i.east.wkdir)

Back to Topic Listing

To create a new database user, perform the post-obit steps:

1.	Open up Oracle SQL Developer from the icon on your desktop.
ii.	Select View > Connections.
3.	In the Connections tab, right-click Connections and select New Connectedness. A New / Select Database Connection window will appear.
4.	Enter system_orcl in the Connection Name field (or any other proper noun that identifies your connection), system for the Username field, and <your password> for the Password field. Select the Save Password cheque box. Enter <hostname> in the Hostname field and orcl in the SID field. Click Test.
5.	Check for the status of the connectedness on the left-bottom side (higher up the Aid push button). It should read Success. To salve the connection, click Connect. Shut the window.
half dozen.	The connection is saved and you lot tin can see it listed under Connections in the Connections tab.
7.	Expand the system_orcl connexion. Note: When a connection is opened, a SQL Worksheet is opened automatically. The SQL Worksheet allows you to execute SQL against the connection you just created.
eight.	Enter the following code in the SQL Worksheet to create a user for the migration repository. CREATE USER MWREP IDENTIFIED BY mwrep DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; GRANT CONNECT, Resource, CREATE SESSION, CREATE VIEW TO MWREP;
9.	Run the script , using the "Run Script (F5)" icon.
10.	The mwrep user was created successfully.

Back to Topic List

To convert the Microsoft Access XML to Oracle, yous demand to create a repository to store the required repository tables and PL/SQL packages. To do this, perform the following steps:

1.	Before you lot create the repository, you demand to create a connection to the mwrep user. In the Connections tab, right-click Connections and select New Connection. A New / Select Database Connection window will appear. Notation: If this tab is not visible, select View > Connections.
2.	Enter mwrep_orcl in the Connection Name field (or any other proper name that identifies your connectedness), mwrep for the Username and Password fields. Select the Relieve Password cheque box. Enter <hostname> in the Hostname field and orcl in the SID field. Click Test.
3.	Check for the condition of the connection on the left-bottom side (above the Assistance button). It should read Success. To salvage the connection, click Connect. Close the window.
iv.	The connection is saved and you can run across it listed under Connections in the Connections tab.

5.	Correct-click the mwrep_orcl connection and select Migration Repository > Acquaintance Migration Repository.
6.	A progress window appears.
7.	Click Close when the install completes.
8.	Click OK.

Back to Topic List

The procedure for exporting the Microsoft Access database file to an XML file has been completed for you and the XML file is bachelor in the Capture directory. To view this process, click here.

To capture the Microsoft Access exported XML file into Oracle SQL Developer, perform the following steps:

1.	Select Migration > Capture Microsoft Admission Exported XML.
2.	A Microsoft Access Capture dialog box appears. Click Browse....
iii.	Browse the Capture directory and select the Northwind.xml file.
4.	Click OK.
v.	The objects are beingness captured. When washed, click Close.
half dozen.	Northwind (Access) is listed in the Captured Models tab. Expand Northwind (Access).
vii.	Aggrandize Northwind to see the list of objects that were captured.

Back to Topic List

It is of import to review the conversion preferences at this signal. To do so, perform the post-obit steps:

1.	Select Tools > Preferences.
2.	Expand Migration and select Identifier Options.
iii.	Make sure Is Quoted Identifier On is selected. Click OK.

Dorsum to Topic Listing

To convert the captured model to the Oracle model, perform the following steps:

i.	Right-click the captured model Northwind (Admission) and select Convert to Oracle Model.
2.	The Fix Information Map window appears which shows y'all the Source Data Blazon and what it will exist converted to in the Oracle Model. Click Apply.
3.	The conversion is performed. When washed, click Close.
4.	Expand Converted:Northwind (Admission) listed in the Converted Models tab.
v.	Aggrandize Northwind to view the converted objects

Dorsum to Topic List

To generate the SQL script with DDL statements that will be executed to create the objects in an Oracle Database, perform the following steps:

1.	Right-click Converted:Northwind (Admission) and select Generate.
2.	The Oracle SQL is being generated. When done, click Close.
3.	The SQL from the script is shown. Select system_orcl from the drop-downward on the correct. icon.
4.	Click the Run Script (F5).
v.	The results are brandish from the script execution.
vi.	Now that your scripts have run successfully, yous tin create a connection for the Northwind user. Right-click Connectedness and select New Connectedness.
vii.	Enter northwind-migrated_orcl in the Connection Proper noun field (or whatsoever other proper name that identifies your connection), Northwind for the Username and Password fields. Annotation that the password is instance sensitive. Select the Save Countersign bank check box. Enter <hostname> in the Hostname field and orcl in the SID field. Click Test.
8.	Check for the status of the connectedness on the left-bottom side (in a higher place the Help push). It should read Success. To save the connection, click Connect. Close the window.
9.	Expand the northwind-migrated_orcl connection.
10.	Expand Tables.
11.	The database tables that were converted to Oracle are listed. Select the EMPLOYEES table.
12.	Select the Data tab. Detect that currently in that location is no information in the table. You will migrate the information later in this tutorial.

Back to Topic List

A appointment format masks tin can exist specified in the preferences and so that the Offline Data Move scripts and in particular the Oracle SQL*Loader control files tin reference the correct format. To do so, perform the following steps:

one.	Select Tool > Preferences.
ii.	Expand Migration and select Data Move Options.
3.	Make sure the Engagement Mask is the following. yyyy-mm-dd HH24:mi:ss.ff3
iv.	Brand sure the Timestamp Mask is the following. yyyy-mm-dd HH24:mi:ss.ff3 Click OK.

Back to Topic List

The migration from Microsoft Admission to an Oracle database tin can exist straightforward. Just information technology is worth noting that some objects and syntax are non automatically migrated. Therefore manual intervention is required.

Analysing the Capture Model, identifying the number, type and complication of objects , can help calculate the estimated time required for manual tasks.

The following Reports can be used with your own task and project estimation.

• Migration Reports> Migration Summary
• Migration Reports> Migration Details
• Migration Reports> Automatic Proper name Changes
  

Judge fourth dimension to:

• Resolve each object that failed to convert manually
• Verify and test each object
• Make changes to the application due to name changes

Back to Topic Listing

The information has already been exported into files from Microsoft Access. To view the steps, click here.

To import the data using the scripts provided, perform the following steps:

1.	Open a DOS command prompt and execute the following commands: <prompt> cd <location where files are> <prompt> oracle_ctl The files are located in the Information directory where you unzipped the files provided in the prerequisites section. oracle_ctl is a bat file that contains statements to load the data. It uses sqlldr to load the data.
2.	The command executes successfully.
3.	Switch to Oracle SQL Developer and Refresh the connection.
four.	The data for the tabular array has been loaded successfully.

Back to Topic List

Depending on the complexity of the database being migrated, a large part of the migration project could be testing.

Currently Oracle does non provide whatsoever tools that can automatically validate a migrated database for yous. This phase of the migration should be planned and taken into account before the migration begins.

The post-obit should be part of your project plan

• Verify Database Structure
• Verify Database Security
• Verify Data
• Verify Logical Correctness of Views
• Test Application

Back to Topic Listing

In this tutorial, you learned how to:

	Create the mwrep User
	Create the Migration Repository
	Capture the Microsoft Access Exported XML
	Check Convertion Preferences
	Catechumen to the Oracle Model
	Generate and Execute the Script to Create the Oracle Database Objects
	Check Offline Information Move Preferences
	Migrate the Data

Dorsum to Topic List

Place the cursor over this icon to hide all screenshots.

brooksyeadeve.blogspot.com

Source: https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/hol08/sqldev_migration/msaccess/migrate_microsoft_access_otn.htm

Share this post