Oracle SQL Developer 2.1: An Overview and New Features

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

Agenda

• Reviewing SQL Developer Features – – – – – – – – –

Database Connections Database Objects Schema Data PL/SQL Reports Unit Testing Migration Workbench Version Control Data Modeling

• Summary



SQL Developer Features

• • • • • • • • • • • • • •

Creating database connections Creating and browsing objects Querying and updating data Using the SQL Worksheet Running the Query Builder Importing data, exporting data and objects Creating and running PL/SQL Running and defining reports Building extensions Creating PL/SQL Unit Tests Integrating with TimesTen Integrating with Oracle APEX 3.0.1+ Working with files and version control Browsing and migration from third-party databases

SQL Developer 1.5.x Features SQL Developer 1.5.5 ships with Oracle Database 11g Release 2

• Database connections – OS authentication, Proxy, LDAP – Force thick driver usage – Group connections in folders

• Navigator support for – – – –

Queues, Queue tables Browse and load Java Generating DB documentation Managing the database

• Schema copy and compare • Create new table on XLS import • Tuning support – Hierarchical Profiler – GUI view of trace files

• SQL Worksheet includes – – – – –

Updated SQL formatter Code refactoring options Increased code insight Code templates Flashback

• Extended search • Integrated support for versioning and source control systems: – CVS and Subversion

• Migration – Sybase Adaptive Server

• Integrated support for TimesTen

SQL Developer 2.1 – New Features

• • • •

PL/SQL Unit Testing Data Modeler Viewer Migration support for IBM DB2 UDB and Teradata Updated Data Grids – Manage columns, filter on data

• New SQL Worksheet – Dockable dbms_output, multiple worksheets

• Increased Connections navigator support for – Jobs, Editions(Oracle Database 11g Release 2), XML DB Repository

• Updated display editors – PL/SQL edit mode, subpartitions

• Version control support for Serena Dimensions, Perforce • Updated filtering mechanism – Schema level, generated objects

Getting Started: SQL Developer 2.1 Certification

• Microsoft Windows – – – – –

• Oracle Database Certification

Windows 2000 Windows 2008 Server XP (Professional) Vista Windows 7

• Apple MAC – OS X (Power PC)

• Linux – Oracle Enterprise Linux 4 – Red Hat Enterprise Linux – SUSE Linux Enterprise Server

Oracle Database 11g Release 2 Oracle Database 11g Release 1 Oracle Database 10g Oracle Database Express Edition 10g Oracle 9i ( 9.2.10.1 and above) Oracle TimesTen In-Memory Database 11g and 7.0 – Oracle In-Memory Database Cache 11g and 7.0 – – – – – –

Database Connections

• Connection Support Basic, TNS, LDAP, Advanced External authentication Proxy connections Third-party database Password expiration Use thick driver for strong authentication – Kerberos Authentication – – – – – –

• Create, save and test – Multiple databases – Multiple schemas

• Import and export • Reuse – Database browsing – PL/SQL development – Running reports

Grouping Connections

• Logically group connections using folders • Create and manage folders • Add new connections to folders • Create local connections

Creating and Browsing Objects

• Connection Navigator - Tree based object browser • Context menu utilities • Create and edit database objects – – – – – – – – – – – – –

Tables, Views, Indexes, Sequences Packages, Procedures, Functions, Triggers, Types Materialized Views and Logs Synonyms (public and private) Directories, Database Links (includes Public) Application Express Queues, Queue Tables Browse and Load Java XML Schemas, XML DB Repository Jobs Editions (Oracle Database 11g Release 2) Recycle Bin Other Users

• Generate DB Doc • Run Manage Database report

Schema Copy and Compare

• Schema Copy – – – – –

Create new objects Truncate objects Drop objects Summary report of actions Log output to SQL Worksheet

• Schema Compare – – – – –

Specify object types Specify objects Review diff summary Summary report Statements sent to SQL Worksheet

Data Grids: Querying and Updating Data

• Browse table and views data grid – Filter, Sort

• Context menus offer – – – – –

Single record view Duplicate row Count rows Save Grid as a report Highlight data

• Manage columns – Reorder – Hide and show columns – Data filters

• • • •

Track updates in message log Recall filters Single value updates in data grid Clear grid settings

Exporting and Importing

• Context menu DDL and data export – Text, CSV, Insert, Loader, XML, HTML, XLS

• Import Data – MS Excel data import – Create table on import

• Export wizard – Parameters to include: • Schema owner • Drop statements • Storage syntax • Inline constraints syntax • Export data • Export grants – Export opens in SQL Worksheet

Using the SQL Worksheet

• Execute queries, scripts – Single statement execution – Script execution

• Editing statements – – – –

Syntax highlighting Code insight Code formatting Bind variable support

• Supporting windows – – – –

Command history Explain Plan Autotrace HTP and OWA output

• SQL*Plus commands

SQL Worksheet

• SQL Formatter – Preview layout

• Code refactoring options • Code insight • Drag and Drop choices – Select for individual statements – Select and join – Insert, update, delete

• Recall window sizing • Flashback(Oracle Database 11g) • Multiple unshared worksheets – Ctrl-shift-N

• Dockable SQL History • Code templates • Code snippets

SQL Query Builder

• Drag and Drop Query Building – – – – – – –

Context menu option Multiple schema view Automatic FK joins Complex where clause Preview SQL Preview results Add query to SQL Worksheet

Code Snippets and Templates

• Code Snippets – Drag and drop code from snippet categories to SQL Worksheet or code editor – Start typing to invoke code insight – Create new • Categories • Snippets

• Code Templates – Add ID and code in preferences – In SQL Worksheet, start typing code to invoke code insight

Working with PL/SQL

• Create and edit PL/SQL code – Code editor • Syntax highlighting • Code formatter • Code insight (auto complete) • Code folding • Query Builder – Code snippets and Templates

• Use dialogs to create – Procedures, functions, triggers – Package spec & body

• Compile and run PL/SQL • Debug – Remote debug with environment variable SET ORA_DEBUG_JDWP

Creating and Running Reports

• Shipped Reports – – – –

DBA System Oracle APEX Migration

• User Defined – – – – – – –

Charts Gauges Master/detail Drill down Script HTML Code

ASH and AWR Reports

File Based Development

SQL Developer supports file based development • Explore files in the Files navigator • Open files in the SQL Worksheet • Place files under version control

Integrated Version Control Support

• SQL Developer provides integrated support for versioning and source control systems – Subversion

• Use Check for Updates to add integrated support for – CVS (Concurrent Versions System) – Perforce – Serena Dimensions

• Access using – View > Team > Versioning Navigator – Versioning menu

PL/SQL Unit Testing

• • • • •

Library Static and dynamic lookups Reports Suites Tests

• • • •

Multi user repository based Code coverage Use the command line Target any database

Integrated Data Modeling

• Open existing models • Drag and drop tables to create relational model • View Design Rules • Inspect all properties • Browse with thumbnail view • Read only

SQL Developer Migration Workbench

• Integrated migration environment • Connect to non-Oracle databases – MySQL, Microsoft SQL Server, Microsoft Access, IBM DB2, Sybase Adaptive Server and Teradata

Create database connections and browse objects Migrate database objects and data Access the SQL Worksheet and perform basic ANSI SQL Quick Migrate Utility provides a one-step migration of database objects and data to Oracle • Manual migration alternatives offer step-by-step migration • Scratch Editor • • • •

– Translation of T-SQL, MySQL or MS Access to the Oracle equivalent

SQL Developer and Oracle APEX 3.0.1+

• Connect to and browse all Oracle APEX applications associated with a database schema – Application level – Page level

• Run reports on: – – – –

Workspaces Applications Pages Schemas

• Context menu support for – – – –

Importing, exporting Deploying Dropping Modifying applications

SQL Developer and TimesTen

• Support both In-Memory Database Cache and TimesTen InMemory Database with direct-linked and client/server connections • Browse, edit, create, modify TimesTen database objects • View the properties and SQL definitions of Cache Groups and Replication Schemes • Access SQL Worksheet for ad-hoc SQL queries, execute built-in procedures and ttIsql commands • Manipulate cached data in both TimesTen and Oracle at the same time • Export data in Oracle and TimesTen using ttBulkcp format • Available with SQL Developer 1.5.x and above

Extensible Framework

• Easily extended – Based on the JDeveloper IDE

• Oracle custom extensions: – – – – –

Migration Workbench TimesTen Data Mining Rdb (under development) Spatial (under development)

Finding More Detail www.oracle.com/technology/products/database/sql_developer

• SQL Developer on OTN – White papers,Oracle by Example (OBE) and online demos – Team Blogs: Blogs, Magazine Articles & Podcasts – http://www.oracle.com/technology/products/database/sql_developer

• SQL Developer Exchange – Share reports, snippets, code, and add feature requests – http://sqldeveloper.oracle.com

• Forums – SQL Developer http://forums.oracle.com/forums/forum.jspa?forumID=260

• Migration Sites – http://otn.oracle.com/migration

SQL Developer Exchange http://sqldeveloper.oracle.com

Summary

Lightweight, easy install Browse schemas, users and data Create and update objects Create, execute and debug PLSQL Run and create user defined reports Browse and migrate schemas from third-party databases • Browse Oracle Application Express 3.0.1+ content • Place files under version control • Extend development environment • • • • • •

For More Information

http://search.oracle.com SQL Developer

or http://www.oracle.com/technology/products/database/sql_developer