Oracle SQL Developer: You're Doing it Wrong!

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | SQLDevYou’re Doing it Wrong Jeff Smith Senior Principal Product Manager Jeff.d.smith@oracle.com || @thatjeffsmith Database Tools, Oracle Corp

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle SQL Developer, Major Feature Areas  PL/SQL IDE  SQL editor  GUI for browsing and managing database objects  Ad Hoc reporting  Database Design & Data Modeling  Database Administration  3rd Party RDBMS Migrations to Oracle  Deploy and Administer Oracle REST Data Services  REST Enablement of the Database  Command Line Interface

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 6 SQL Developer Snapshot Time Tested Launched in 2005 An Industry Standard 5M+ Oracle DBAs & Developers Always Improving Quarterly Releases 4

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Project Raptor  Early Adopter on OTN 2005 1.1  File Based PL/SQL editing  Reports  Ships with 11gR1 1.5.x  Localizations - Japanese, Spanish, Italian, German, French, Portuguese, Chinese, & Koreans  Ships with 11gR2 SQLDev v1.0  SQL Worksheet  Procedure Editor  The world takes notice! 1.5  Schema Copy/Diff/Export  Thick connections  Versioning  TimesTen 2.1  Unit Testing  Data Modeler Viewer  Unshared SQL Worksheets 2007 201020092006 3.0  Full Data Modeler  View > DBA  SQL Tuning Advisor  Query Builder 2008 2011 2012 3.1  New Database DIFF  PDF Reports  RMAN  Data Pump 2013 3.2  APEX Listener / ORDS Support  Database 12c Support  Schema Service Support 4.0  Java 7  ASH/AWR/ADDM  Color Coded Connections  Command line Interface 4.0.3  Optimized Code Insight  Oracle Big Data Appliance 2014 2015 2016 4.1  ORDS Included/Full GUI  instance Viewer  SQLcl  Copy PDB to DBaaS  Larry live demos PDB relocate @ OOW 4.2  Real Time SQL Monitoring  Top SQL Report  Formatter & Other Editor Enhancements  Easier RESTful Services Development 2017 SQLcl  New CLI  Modern SQL*Plus Release History

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 2017 2017.2  Bug Fixes  Sharding  Data Guard 17.3 & 17.4  Bug Fixes Quarterly Releases!!! 2018 Release History 18.1  Autonomous Data Warehouse Support  New Welcome Screen/Connections  SQL Injection Detection  PL/Scope SQL Statement Support  Better Formatting  KILL ALL THE BUGS! 18.2/3  PL/SQL Injection Detection  Cloud data Loads  Nested Connections

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Huge Product, Most Folks are Self-Taught… …it’s our fault, not yours.  PL/SQL IDE  SQL editor  GUI for browsing and managing database objects  Ad Hoc reporting  Database Design & Data Modeling  Database Administration  3rd Party RDBMS Migrations to Oracle  Deploy and Administer Oracle REST Data Services  REST Enablement of the Database  Command Line Interface

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | SQL Statement & Script History – access with Up/Down Key  Sensitive commands, connect black-listed  Defaults to last 100  Avail from session to session per OS user

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Auto-Replace/Spell Checking in SQLcl  Uses SCRIPT/JS  Looks for ‘bad’ text  Replaces before sending to JDBC  Shows corrected SQL with Output

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Wrong Way: Digging around the Connections Tree Tree Basics:  use the keyboard to navigate  use connection folders  hide things! OR  don’t use the tree at all!

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Right Way: Try the Schema Browser Right-click on your Connection, open the schema browser  drop down pickers vs tree  full features  client side filtering

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Wrong Way: Loading Scripts/Copy/Pasting Code Code your frequently used SQL and scripts directly into the product: Code Snippets  …or Auto-Replace  Reports

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |  Automatic = suggestions as you type  Manual = suggestions as you ask for them (ctrl+space)  drag and drop from tree to generate code Wrong Way: Manual Coding

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |  Adjust delay  Suppress automatic suggestions OR  Turn off completely and use keyboard, on demand with CTRL+Spacebar Wrong Way: Getting Mad at the Pop-ups

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Bonus: Get Help/Examples with Commands!  peak into Docs for sample code on your function/command  click on ‘book’ to go straight to the DOCS page for that function/command

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Plans/AutoTrace – generate & compare side by side! Hit the drop-down control next to Plan Button to access cached plans from v$sql_plan Pin Plan, Gen Plan, Right-Click > Compare for Side By Side View

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |  Add Comment  Execute via F5 (Script)  Comment labels match grid export formatter labels  ONLY works in SQLDev… & SQLcl  Right Way: Write Straight to CSV + Spool

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Wrong Way: Running on Default Preferences  Open Object on Single Click  Code Editor Font  Grid in checkerboard  Display NULL As  Shortcut Keys  PL/SQL Syntax Colors Ten Minute Video Right way: tweak SQLDev to work the way you want it to

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Watch Database Activity  agent-less  ZERO DB objects  a ‘fancy’ report  no history ● no alerts ● drill-downs We’re making more of these 

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Wrong Way: Data In and Out of Excel  XLS vs XLSX  Importing  External/SQLDR  Ctrl+C vs Shift+…  Multiple tables to different workbooks

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Right Way: Some Excel Tips  XLSX faster than XLS  CSV faster than XLSX  CSV => External Table better for large imports  Ctrl+Shift+C => Copy Data + Column Headers  Export multiple tables to a single XLSX file = use the Database Export Wizard

Oracle SQL Developer: You're Doing it Wrong!

Oracle SQL Developer: You're Doing it Wrong!

More Related Content

What's hot

Similar to Oracle SQL Developer: You're Doing it Wrong!

Recently uploaded

Oracle SQL Developer: You're Doing it Wrong!