Step-By-Step Guide to Importing Data from Oracle XE to SQL 2005

Many enterprises need to move data from one kind of data source to another. This tutorial guides you through the steps necessary to move data from Oracle XE into a SQL 2005 Server database.


In a previous step-by-step tutorial, I discussed how to install and work with Oracle’s 10g based small-footprint express edition, Oracle XE for short. Specifically I showed how to work with one of the existing databases. It’s quite an exhilarating experience to experiment with this fast, easy to use database that only requires modest resources but can store up to 4GB of user data (limited to one CPU on your machine). This is an excellent resource for those who want to play with the bigger versions; they can train on this one.

Enterprises always have more than one kind of data source involved in their business and at times they do need to move data from one to another, for archiving, creating user interface objects in a data bound application, some off-line simple analysis in their spread sheet applications, and so forth Data movement is also driven by software evolution. For example, an existing database program can only handle relational data, but the company is proactively looking at using XML as well. They need to move data to a platform that handles XML, preferably both XML and relational data.

This tutorial is more about SQL 2005 Server and less about Oracle XE. This tutorial guides the reader through the steps necessary to import data from Oracle XE into a SQL 2005 Server database. The tutorial will use the Import/Export wizard to bring a couple of tables from Oracle XE to a new database in SQL 2005 Server. The various steps are clearly delineated. The next picture shows the Object Browser in Oracle XE displaying the table list in the hr database. Tables enclosed in red rectangles will be brought into SQL 2005 Server.

{mospagebreak title=Call up the wizard}

Right click on the Management folder and choose the Import Data … menu item from the drop-down.

This wakes up the Import/Export Wizard as seen in the next picture. You can see that wizard packs a lot of tricks. It creates packages that do the final trick.

The source of the data

When you click on the button Next in the previous window, the window shown next, SQL Server Import and Export Wizard,  pops up. In this window, you must indicate where the data to be imported is presently residing. The defaults assumed are the local SQL 2005 server (Hodentek/Mysorian); Windows Authentication; the default datasource – SQLClient and a database <default> as shown here.

Since the data resides in Oracle XE, click on the drop-down for the data sources and choose the Microsoft Oledb Provider for Oracle as shown here.

After making the above choice, if you click on the Next button you will face the next window, where you need to provide the Oracle XE related information. It is assumed that Oracle XE has already been started, otherwise you may get a not found error. For the Data Source: textbox you enter xe (not case sensitive), for the user name and password you enter hr, hr (forget Scott/Tiger). Also place a check mark in the Allow saving password checkbox. You may also verify that the connection was successful by clicking on the Test button.

{mospagebreak title=The destination of the data}

The destination for purposes of this tutorial is the SQL 2005 Server (Microsoft SQL Server Management Studio 9.00.1399.00). When you click the OK button to the previous screen you get the following window.

Since the tables imported will go into a database in SQL 2005, you need to specify  an existing database, or create a new one. The server name is automatically entered as shown. Click on the ellipsis button, New … in the above screen to bring up the Create Database window shown in the next screen.

You need to give a name to this database, which is Imported10g for use in this tutorial. The rest of the information gets filled in as shown. It essentially creates the data file Imported10g_data.mdf and log file Imported10G_log.ldf in the correct directories. As to the file sizes and their growth, you may make changes should you so desire. Here the defaults are used.

When you hit the OK button you have completed configuring the destination for the data, which is shown in the next screen. The database Imported10g will be created if everything goes according to the plan.

{mospagebreak title=The nature of the data transferred}

When you hit the Next button in the previous screen you will bring up the next screen, which requires you to specify what kind of data you want transferred (integrated, maybe). There are two options, to copy the tables or to bring in only what is required by writing a suitable query. For this tutorial we will be bringing a couple of tables from Oracle XE to SQL 2005 Server, so the first option is chosen.

Tables and views to copy are chosen

When you click on the Next button in the previous screen you will be presented with the next screen, where all the tables and views in the Oracle XE are shown together with individual check boxes where you may make the selection of tables/views you want to bring. For this tutorial the four tables shown earlier will be brought in.

It is also possible to edit the information transferred by hitting the Edit… button to make changes to the mapping. You also have the option of getting all the tables. For a selected table, highlighting it in the list and hitting the preview button will show the data it contains, as shown in the next screen. To exit out of this, click on the OK button.

{mospagebreak title=Saving and executing the package}

When you click on the OK button in the above screen, you will reach the stage where you may want to save the package for future use, execute the package immediately, or both save and execute the package. The last option is chosen in this case, according to the choices shown in the next screen. There are also two choices while saving: saving it to a file in the file system, or saving it in the MSDB folder in the Integrated Services server. MSDB is a sub-folder in the Stored Packages folder.

Protecting the package

When you click on the Next button you will be asked for the option you want to choose for protecting the package. There are several options including password and encryption. For the tutorial the Do not save sensitive data option was chosen.

When you click on the Next button in the above screen, your chosen features for saving before the final save action is shown. Like most of the other screens, you have a chance to back off at almost every step of the way. Here you may also make a choice of the authentication required for the package. The default, Windows authentication, is chosen here.

{mospagebreak title=Wizard’s Summary report}

When you click on the Next button you will see the following screen, which summarizes the actions that will be taken by the wizard.

Package execution

When you click on the Finish button, the package will be executed. It will show the following screen, which will be progressively filled as each step of the package is executed. Error messages are shown as hyperlinks for the error message content in the message column, should they appear. The hyperlinks may also be read for other messages.  No errors were reported for this package.

Result of import

This next window shows the screen shot of the fully expanded 10g database in SQL Server 2005’s Management Studio. In the next section the same tables in SQL Server 2005 and Oracle 10g are compared.

{mospagebreak title=Comparison of Department and Employees tables after importing}

The next set of pictures shows the tables in their own platforms so that you can make comparisons. It looks like SQL 2005 did a pretty good job with the copying of the columns. However, the primary key information is lost and so are the indexes, and more. A detailed discussion of the differences is outside the scope of this tutorial. The NUMBER data type in Oracle XE goes over directly to the Decimal data type in SQL 2005 Server; similar differences exist for varchar and varchar2 data types. To quote from their documentation:

Oracle XE
NUMBER [(precision [, scale]]) Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.
Sql 2005

The decimal data type can store a maximum of 38 digits, all of which can be to the right of the decimal point. The decimal data type stores an exact representation of the number; there is no approximation of the stored value. The two attributes that define decimal columns, variables, and parameters are: p Specifies the precision, or the number of digits the object can hold. s Specifies the scale or number of digits that can be placed to the right of the decimal point. p and s must observe the rule: 0 <= s <= p <= 38. The default maximum precision of numeric and decimal data types is 38. In Transact-SQL, numeric is functionally equivalent to the decimal data type.


To explain this by way of images, compare this one from Oracle XE:

with this one from SQL 2005 Server:

Likewise, compare this image from Oracle XE:

with this one from SQL 2005 Server:


SQL 2005 Server’s Integrated Services does a pretty good job of data transfer as shown here. The comparative view of tables shows some of the differences. A similar comparison for other tables and objects should give you a good idea of how data is handled in the two database programs, an invaluable aid if you are interested in ETL. There are quite a number of differences between DTS and SSIS as you might have noted, assuming you experienced the earlier version. Oracle XE is also a cool package, free, and can be upgraded easily from this version. You may also notice that Oracle XE gives you a lot more information on a table than SQL 2005 does, all in one screen.  Thus begins the battle of the titans.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye