Data Definition Language (DDL) is used to create our database objects. You may need to get this DDL code for an object you’ve lost the ‘source code’ for, or perhaps you want to build an script to create something you’ve been putting together with the GUI.

So we have our table, but how would we get the DDL code?

In today’s post, I’ll demonstrate all the different way to accomplish this task using SQL Developer Web. It’ SQL Developer, but in your browser!

So let’s start with an object, in this case my NETFLIX table.

My NETFLIX table and the DDL behind it.

Let’s use code to generate code!

Use the DDL command in the SQL worksheet

Try this with one of your tables.

SET DDL / SHOW DDL – those are SQLcl settings that work across all of our tools. And the ‘DDL’ command generates DDL for an object.

SET ddl constraints off
SET ddl ref_constraints off
SET ddl tablespace off
SET ddl segment_attributes off
ddl NETFLIX
Use the Execute as Script button

The output is sent to the Script Output panel.

Using the Create/Edit Dialogs

Find your table.

Right click on it.

Choose ‘Edit’

Then click on the DDL page on the left, and the ‘Create’ tab on the slider panel.

If you were to make any changes to the object, you would see those as DDL changes in the ‘Update’ panel.

Using the Modeler

It’s not JUST for making pretty pictures!

From the Modeler page, simply press this button and wait. It can take a minute or so…

This will generate the DDL for every single object in your schema.

If you want it for just a few objects, the easiest thing to do is build a model diagram, then ask for the DDL for those objects.

So add your objects, I just drag and drop them over.

I know, I know, where are all the relationships?

Then the DDL Preview button gives me this –

Options, oh tell me more!

If we look into the options –

Pick and choose what makes you happy.

The Movie

Generating DDL in SQL Developer Web’s Data Modeler

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

4 Comments

  1. Thanks Jeff, that would be awesome. How best to keep abreast new features as they are implemented into SQLcl?

  2. Is there any plan to enable DDL for a user?

    Often just want to see how a user is created with permissions etc, or to create a similar user.

    The dbms_metadata sub programs are already there (e.g get granted_ddl) so shouldn’t be too difficult right? 🙂

    • Correct, it’s not hard. I’ll add to our list ‘Clone/Create Like’ and ‘DDL’ to the User actions.

Write A Comment