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 ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET DEFINE ON
CLEAR SCREEN
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
@passworddefinition.sql

PROMPT Creating Role
@role.sql

prompt creating user Emulation
@@Emulation/user.sql

prompt creating user dbo_Northwind
@@dbo_Northwind/user.sql

prompt creating user dbo_pubs
@@dbo_pubs/user.sql

prompt Building objects in Emulation
@@Emulation/master.sql

prompt Building objects in dbo_Northwind
@@dbo_Northwind/master.sql

prompt Building objects in dbo_pubs
@@dbo_pubs/master.sql

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.