Monday, 7 October 2013

Convert SQLServer to Oracle using files - Part 3

In part 1 we generated the offline capture scripts to take to the SQL Server machine, unloaded the metadata, zipped it up and copied it back to out local machine. In part 2 we used SQL Developer to create a migration project and load the capture files into SQL Developer.  We then converted the metadata into its Oracle equivalent.
In this episode we will try and generate DDL from our migration project.  Right now, We can see the Oracle objects in the Converted Database Objects node.
If we right click on Converted Database objects and choose generate, we can generate DDL to create the Oracle Schema and Objects.

The wizard appears again with the introduction screen.  Clicking next takes us directly to the Target database Screen.

Click on offline to choose generation of files.  For specifics of how the files get generated, click on advanced options

 You can select what way you want to generate your files, all in one file, a file per object type or a file per object. You can also choose the types of objects you want to generate and run.
 In this demo, I will just generate tables, data and supporting objects.   Clicking next  will take us to the data move page where we again choose offline to generate files.
 Choosing advanced options allows us to be specific about date masks and delimiters for data unload.
 Once we have chosen our options, we click next and review the summary.
 Finally, we click finish and the files are generated in the output directory we specified when setting up the project in part 2.
Now, Lets go see what we generated.  If we go to the output directory we specified in the project, we can see the list of files we generated.  Remember the options we chose for generation.
We also get the master.sql file opened in SQL Developer which looks like this

set serveroutput on

COLUMN date_time NEW_VAL filename noprint;
SELECT to_char(systimestamp,'yyyy-mm-dd_hh24-mi-ssxff') date_time FROM DUAL;
spool democapture_&filename..log

-- Password file execution

PROMPT Creating Role

prompt creating user Emulation

prompt creating user dbo_Northwind

prompt creating user dbo_pubs

prompt Building objects in Emulation

prompt Building objects in dbo_Northwind

prompt Building objects in dbo_pubs

Now, lets try and run this file and create the users and objects.  Firstly, we choose a connection to run the script.  This user must have the privileges to create users and all their ancillary objects.
We can run this script to create the users.  Notice the worksheet output showing the output of the files.
Once this is complete, we can create a connection in SQL Developer to one of the users created, dbo_Northwind, dbo_pubs and emulation.

Now, we have created the schema from the DDL which was generated.  In the next and final episode of this, we will visit the data move.  We will run the data move scripts on SQL Server and extract the data which we can load via SQL Loader or external tables.

No comments: