November 08, 2013

Generate Oracle-Database from EDMX

How to create an Oracle database using the ADO.NET Entity Data Model Designer

The context

Our current project is about a central register of vehicles which need to be transferred into a legacy system once created. The integration service will replicate the entries into the clients huge database landscape (kind of multiplexing). For developing purposes, it would be ideal to have a cloned system in our house, but because of the landscapes size we cannot simply dump the system and clone their ambient into our developing ambient. Furthermore, we have only very few tables for the vehicle register.
The idea is to:

  1. generate entities from the existing database the client provides (Oracle DB).
  2. create a database in our ambient, only focused on the necessary entities.
The starting point for this post is an already created conceptual model using the ADO.NET Entity Data Model generator.

Prerequisites

For this tutorial you need at least MS Visual Studio 2010 and the Oracle Developer Tools for Visual Studio installed on your machine. I suppose that you have already a conceptual model at hand. So, you find yourself in the following situation:

Setting up Oracle PL/SQL generator

To generate PL/SQL you need to enter the properties of your data model (right click in the designer). At least, you need to setup up the Database Generation Workflow and DDL Generation Template like it is depicted below. You may want to setup other things, most probably the Database Scheme Name.

Generate Oracle PL/SQL

So, you're nearly done. Just open the context menu in the designer and select Generate Database from Model..., which immediately opens a generated PL/SQL. When I try to finish the process my VS hangs. So, I simply copied the text directly in sqldeveloper and executed the script without any problems.
That's all. Nothing more, nothing less.