1 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

Oracle SQL Developer Tips & Tricks

Jeff Smith Senior Principal Product Manager

Agenda

 A Brief History of the Product  Talk the Tip 

Show the Tip

 Slides + Live Demo  Questions at the End 3 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

Oracle SQL Developer Overview

 de facto Oracle IDE/GUI  3,500,000+ users worldwide  Windows, OS X, *NIX  Installed with Oracle Database  MOS via DB license 4 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

SQL Developer Release History

 Project Raptor (2005) ~ Oracle SQL Developer v1.0 (2006)  Version 1.5 (2008)  Version 2.1 (2009)  Version 3.0 (2011)  Version 3.1, 3.2 (2012)  Version 4.0 (2013) 5 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

Support for Other Oracle Database Products

 Times Ten  Data Miner  XML DB  Spatial & Graph 6 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

SQL Developer Data Modeling Included for free, also available as a separate download/program Strategy and Analysis Import Models Data Type Domains ERD Logical DFD Database Design Multidimensiona

l

Relational Star Schema Physical

7 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

Reporting

1. Configure Your Preferences

 Make things look nice  Disable unused extensions  Automatic can be ‘bad’  I like ‘more’ grids 8 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

2. Recall Previous SQL via the Keyboard Fires off of View > SQL History (F8)

9 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

3. Format Query Results Directly to CSV, XML, ...

 Add Comment  Execute via F5 (Script)  Comment labels match grid export formatter labels  ONLY works in SQLDev 10 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

4. Drag & Drop Multiple Tables to Worksheet

11 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

4. Drag & Drop Multiple Tables to a Model

 Shift, Select, Drag, Drop  OR Ctrl+Select, Drag..

 Ctrl walks the dependency tree  Preview DDL, save, export, etc 12 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

5. Viewing PL/SQL Output (Include Ref Cursors)

13 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

5. Don’t Like the Tree? Open a Schema Browser

 Right-Click on a Connection  Drop Down Controls for  Schema  Object  Same Functionality as Tree 14 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

6. Ctrl+Click, Shift+F4, Object Search to Quick Open an Object

 Hold down Ctrl...

 Mouse over object text...

 CLICK to open object editor OR SHIFT+F4 OR View > Find DB Object 15 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

7. Extend SQL Developer With Extensions

 Write your own (XML + SQL)  Check for Updates  Pay $-$$  Free ones too!

16 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

8. Excel Stuff

 Export data to Excel  Import from Excel to table  Existing  Build New One  CSV Exports are Faster 17 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

9. See What SQL #SQLDev is Generating Switch nondebug to debug in sqldeveloper.conf

18 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

10. Split Editors | New Editor Tab Groups Right Click in the Editor Tab, split gives you 2 independent views of the editor, New Tab Group lets you see multiple editors at once

19 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

SQL Developer Resources

OTN – SQL Developer – Forums – By Example Tutorials – My Tips & Tricks Blog Download the Developer Days VirtualBox Image!

11gR2, SQLDEV, APEX, APEX Listener, Times10, OEM and more!

20 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

Cliff’s Jeff’s Notes

 Customize Preferences  Recall SQL History via keyboard  Script Output in CSV, HTML, ...

 Drag ‘n’ Drop Tables to Worksheet  Drag ‘n’ Drop Tables to Model  PL/SQL Output  Schema Browser  Ctrl+Click, Shift+F4, Object Search  Extensions  All of my #SQLDev Posts :) 21 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

Open World Sessions ID

8891 8890 8893 8894 8928 10029 10012 10030 10031 10047

Session

SQL Developer and Oracle Database 12c Creating RESTful APIs with Application Express Listener Implementing Data Redaction with Data Modeler Faster Oracle Database Migrations with 12c Collaborative Development and Building with SQL Developer Performance Tuning with SQL Developer What’s New in SQL Developer 4.0

Data Modelig 101: Oracle SQL Developer Data Modeler Developing REST for On-Premises or the Cloud Oracle Database 12c Features with SQL Developer 22 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

*Marriott Marquis

Time

Tues 12pm Wed 10:15am Wed 11:45pm Wed 5pm Thurs 2pm Tues 3:45pm Tues 5:15pm Wed 1:15pm Wed 5pm Thurs 3:30pm

Location

*Golden Gate B *Golden Gate C2 *Golden Gate C2 *Golden Gate C2 *Golden Gate C2 *Salon 3/4 *Salon 3/4 *Salon 3/4 *Salon 3/4 *Salon 3/4

23 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

24 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.