Monday, September 17, 2012

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.

Thursday, September 13, 2012

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*/