Custom Search

Convert MS Access Northwind database to Oracle

Inside This Article
1. How the data types in Access Northwind are converted to Oracle
2Building Oracle Northwind database objects
3Queries to generate aggregated data for Oracle Northwind database

How the data types in Access Northwind are converted to Oracle

Access Northwind is a popular sample database for learning MS Access. In this article series, we're going to convert the database objects in MS Access Northwind database (tables, indexes, validation rules, primary keys and foreign keys, etc) to Oracle.

Use the links below to download the completed and normalized Oracle Northwind script and the image files.

  • Click here to download the normalized Oracle Northwind relational database script, including table data.
  • Click here to download the images for the Categories and Employees table.

How the fields in MS Access Northwind database are converted to Oracle Northwind database.

Here is a brief overview about the conversion between Access table properties and data types in Oracle tables.

Access Field PropertiesOracle Column Data Type
1AutonumberOracle sequence
2TextVARCHAR2 in variable length of bytes for string values
6MemoVARCHAR2(2000 BYTE)
7OLE ObjectVARCHAR2(255 BYTE) to store the name of the image file such as seafood.gif. The actual image file is stored in file system.
8Yes/NoCHAR(1 BYTE) in ('Y','N')
9HyperlinkVARCHAR2(500 BYTE) for URL string value

1. Autonumber => Oracle sequence

In Access Northwind database, the primary key in a table is normally is an Autonumber field. In Oracle, autonumber field is called sequence.

A sequence is an Oracle object that is used to automatically generate a series of unique numbers such as the autonumber used for Shipper_ID in Access Northwind database. A sequence is normally used as a primary key in Oracle.

Here is the script used to define the sequence for Shippers table.

    MAXVALUE 999999999999999999999999999

If we omit the MAXVALUE option, the sequence will automatically default to 999999999999999999999999999.

When creating the sequence, we can specify a cache value. For example, if we create the sequence with a cache value of 100, it enables the database to store 100 values in memory for faster access. But a system failure can cause these values to be lost and therefore results in a gap in the sequence values when the next insert statement is executed. To avoid this problem, we use NOCACHE.

Note that ALTER SEQUENCE command can be executed to recover the lost sequence values.

We can drop a sequence with DROP SEQUENCE command.


To easily get the next sequence value, use the following command. This is particular useful when coding a client application and we need to know what next sequence value will be so that it can be used as a foreign key value for a child table, eg. when inserting data to parent and child table in one transaction.


2. Text => VARCHAR2

The Text field in Access Northwind database is defined as VARCHAR2 in Oracle with the same length but in bytes.

3. Date/Time => Date

Oracle DATE data type stores information about Year, Month, Day, Hour, Minute and Second. This is sufficient to use for Oracle Northwind database. Oracle TIMESTAMP data type is overkill because it uses more bytes (accurate up to a fraction of milliseconds) than the DATE type.

4. Currency => NUMBER(10,2)

There is no CURRENCY data type in Oracle. We need to use the NUMBER data type in Oracle and specify the precision and scale to create a decimal number. The syntax for this data type is NUMBER(precision, scale), where precision is the total number of digits (including the digits after the decimal point). Scale is the number of digits to the right of the decimal point.

For example, NUMBER(10,2) states that the number has 8 digits to the left of the decimal point and 2 to the right. So it can hold a number up to 99999999.99

5. Number => NUMBER(9)

NUMBER(9) omits the scale part, so the scale is default to zero and as such it stores whole number. It can accept a number with a whole number part of 7 digits, and 2 for the decimal part (after the dot). When data is inserted to a column data type NUMBER(9), it's wrapped to a whole number. So NUMBER(9) denotes that we can store a number like 1234567.99 in the column as 1234568.

6. Memo => VARCHAR2(2000 BYTE)

Memo in Access is a long text data type which can be defined as VARCHAR2(2000 BYTE) or more bytes in Oracle.

7. OLE Object => VARCHAR2(255 BYTE)

OLE Object in Access can be used to store images such as Employee photos. We don't adopt this approach in our Oracle Northwind database. We'll simply make it a VARCHAR2 type for the Photo column and store the image filename such as seafood.gif. The actual image file will be stored in file system, so in our client application that uses the Oracle Northwind database, we need to retrieve the image from the file system.

8. Yes/No => CHAR(1 BYTE) in ('Y','N')

Yes/No data type in Access is converted to a CHAR(1) data type in Oracle. We'll then define a check constraint in the table definition to restrict values in this column to be 'Y' or 'N' only.


9. Hyperlink => VARCHAR2(500 BYTE)

Hyperlink data type in Access is converted to a VARCHAR2 data type in Oracle Northwind database. We will store the website URL in this column.

How to validate Employees birthdate

In Access Northwind database, the data validation rule for the Birthdate column in Employees table is <Date(), which states that birth date can't be in the future. In Oracle, it's invalid to define a constraint like birthdate<sysdate or we'll get an Oracle error message "ORA-02436: date or system variable wrongly specified in CHECK constraint", so we implemented a trigger for BirthDate column to achieve the same effect.

Each time we add or update a birthdate value, the trigger checks the value and if it is a date in the future, the error message from the trigger will be sent to the caller application.

-- Create a trigger to validate employee birthdate.
    if :New.birthdate > trunc(sysdate) then
          RAISE_APPLICATION_ERROR (num => -20000, msg => 'Birthdate cannot be in the future');
    end if;

So far we have explained most of the techniques we used to convert MS Access Northwind to Oracle Northwind. Part 2 of this article series shows the actual scripts for creating tables, sequences, indexes, constraints, etc.


Inside This Article
1. How the data types in Access Northwind are converted to Oracle
2Building Oracle Northwind database objects
3Queries to generate aggregated data for Oracle Northwind database
Other Recent Articles from the Database SQL category:

1.Get single records when duplicate records exist
2.Find duplicate values or non-duplicate values in a table
3.How to get Top 1 record from Oracle by using PL/SQL
4.How to get Top N rows from Oracle by using SQL
5.How to do cross table update in Oracle
6.Export Northwind Access database to MySQL via ODBC

Copyright © 2017 All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

Home | Feedback | Terms of Use | Privacy Policy