Using External Tables in a Reltional Model in Oracle SQL Developer Data Modeler

As of Oracle SQL Developer Data Modeler (SDDM) version 3.1.00.700 you cannot use external tables in Relational Models.  When you import external tables from the Oracle data dictionary into SDDM you get the external tables in the Physical Model, but you cannot use them in a Relational Model.

I have figured out a work around until this is supported in SDDM.  In SDDM create a view which SELECTs each column from the external table.  You can then use the view in Relational Models.  You can name the view in SDDM the same as the external table or differently.  In my scenario I created the view with the same name as the external table and will not create the view in the database.  I am just creating the view in SDDM to represent the external table in relation models.

Microsoft really does not like Oracle

Well I decided to install the "Oracle Developer Day VM" on my work Microsoft Windows XP Pro laptop to play around with Oracle 11g and APEX and got the following message.  Since this was my work laptop I of course aborted the installation.  Microsoft wins this one.

If Oracle's Cost Based Optimizer were a GPS, where would it take you?

Similar to the "If the CBO were a car, what would be it's bumper sticker?"

What would you add to Oracle? response

Lewis Cummingham posed the question, "What would you add to Oracle?" in his blog.  He purposed the ability to support multiple languages within the database.  Thinking completely out of the box, I would enlarge that concept to include support of other database vendors' environments within an Oracle database.  Instead of just allowing users to hop from Oracle over to another database vendor's database via a "gateway", why not just put that other vendor's database inside of an Oracle database.  Oracle could experiment with MySql first because they own it.

I know that this is "Pie in the sky" type of stuff, but you never know.

Oracle SQL Developer Output Hints

I was just reviewing through presentations and papers from the ODTUG KScope 2012 in June and noticed a very nice little feature of Oracle SQL Developer that formats SQL output.  There are hints that SQL Developer recognizes.  The hints cannot contain any spaces.  Also, the hints are case sensitive.  The formatting is done in the "Script Output" window, so you have to use "Run Script" (F5) to execute the SELECT statement.

select /*csv*/ *
  from dictionary
 where table_name like 'USER_TAB%'

would produce the following after "Run Script" (F5):

"TABLE_NAME","COMMENTS"
"USER_TABLES","Description of the user's own relational tables"
"USER_TAB_COLUMNS","Columns of user's tables, views and clusters"
"USER_TAB_COL_STATISTICS","Columns of user's tables, views and clusters"
"USER_TAB_HISTOGRAMS","Histograms on columns of user's tables"
"USER_TAB_COMMENTS","Comments on the tables and views owned by the user"
"USER_TAB_PRIVS","Grants on objects for which the user is the owner, grantor or grantee"
"USER_TAB_PRIVS_MADE","All grants on objects owned by the user"
"USER_TAB_PRIVS_RECD","Grants on objects for which the user is the grantee"
"USER_TAB_MODIFICATIONS","Information regarding modifications to tables"
"USER_TAB_PARTITIONS",""
"USER_TAB_SUBPARTITIONS",""
"USER_TABLESPACES","Description of accessible tablespaces"
"USER_TAB_STATS_HISTORY","History of table statistics modifications"
"USER_TAB_STATISTICS","Optimizer statistics of the user's own tables"
"USER_TAB_COLS","Columns of user's tables, views and clusters"

Hints include:

• /*csv*/
• /*xml*/
• /*html*/ 
• /*delimited*/
• /*insert*/
• /*loader*/ 
• /*fixed*/ 
• /*text*/