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

2 comments:

thatJeffSmith said...

Happy you found that useful!

It's one my favorite tips to show at talks and is one of the most frequently read posts on my blog.

I'm convinced anything that gets folks to their data faster will always be popular.

Thanks for helping get the word out!

Database Scene said...

Retweeted on DS >> https://twitter.com/Database_Scene
Thank you Michael