Top Oracle SQL Developer Data Modeler Settings

PublishedOctober 23, 2017

Comments

No comments yet. Be the first to comment.

More from this blog

SQLcl host Command

Oracle SQLcl is the command line tool to connect to an Oracle database. One neat feature it has is the ability to call host commands (i.e call terminal commands directly from SQLcl). You can use any of the following to trigger a host command: host ho...

Jul 5, 20251 min read

Tips for APEX Application Constants

How to use Text Messages for Constants

Jan 22, 20244 min read

SQL Developer for VS Code

Last week SQL Developer for VS Code was released (direct link to VS Code extension here). If you're not using VS Code (VSC) I highly suggest you download and install as it's the de facto editor for developers (regardless of programming language). I'v...

Jan 22, 20243 min read

How to Generate a sitemap.xml in SQL

I recently had to generate a sitemap.xml for an public facing APEX application. The official sitemap protocol has the following example: <?xml version="1.0" encoding="UTF-8"?> <urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"> <url> ...

Jan 17, 20242 min read

What Happens to APEX Page Items that are Never Rendered?

Every APEX page item has a Server Side condition which controls whether the item is rendered on the page. They're various server side condition options and for the purpose of this article we'll just set it to Never (can do things like queries that re...

Jan 15, 20242 min read

TalkApex

321 posts

Adding PROMPT Statements in DDL

SDDM can generate DDL statements for you either by clicking the DDL button (1) or synchronizing the model with a schema (2):

It's always helpful to have prompt statements before each DDL change to help debug where an issue occurred. To do so open Preferences and check Include PROMPT Command (for Oracle only):

All DDL will then include a prompt statement like:

PROMPT CREATING TABLE 'MY_TABLE'; CREATE TABLE MY_TABLE ...

12c Identity Column or Auto Generate Sequences

Depending on your database version you may want to either have sequences automatically generated for you or not. In my case I was using a 12c instance and was going to use Identity Columns for primary key IDs. I didn't want sequences for each table to be automatically generated. To toggle this feature open Preferences and change the options in the image below.

More on the Identity Column settings here

Short Table Names

In most DB instances all object names are limited to 30 characters (this is changed in 12c but most organizations still haven't enabled 128 characters). When using naming standards this can cause some issues because just referencing <table_name> may not work as its already 30 characters. Note <table_name> isn't a real variable in SDDM.

You can define a short name (SDDM calls it an Abbreviation) for each table via the table properties. Setting the Abbreviation is also very helpful when generating DDL statements or constraints from scripts.

Top Oracle SQL Developer Data Modeler Settings

Comments

More from this blog

SQLcl host Command

Tips for APEX Application Constants

SQL Developer for VS Code

How to Generate a sitemap.xml in SQL

What Happens to APEX Page Items that are Never Rendered?

Adding `PROMPT` Statements in DDL

12c Identity Column or Auto Generate Sequences

DDL Format - Lowercase and Tabs

Naming Standard Templates

Short Table Names

Command Palette

Comments

More from this blog

Adding PROMPT Statements in DDL

12c Identity Column or Auto Generate Sequences

DDL Format - Lowercase and Tabs

Naming Standard Templates

Short Table Names

Adding `PROMPT` Statements in DDL