Oracle SQL Developer 1.5: Feature List

Date: April 2008

This document provides a full listing of features available in Oracle SQL Developer 1.5. (There is a separate New Features for Oracle SQL Developer 1.5 )

Table of Contents

  1. About
  2. Architecture
  3. Database Connections
  4. Connection Navigator
  5. SQL Worksheet
  6. SQL and PL/SQL Editing
  7. Exporting and Importing
  8. Reporting
  9. SQL*Plus Support
  10. Third Party Database Support
  11. Migration Workbench
  12. Oracle Application Express 3.0.1
  13. Preferences
  14. Version Control

1.  About

Download Site OTN
Cost FREE
Oracle Support Available through Metalink for customers with a Database Support contract
Releases

Initial release: SQL Developer 1.0 (March 2006)

  Release SQL Developer 1.1 (Dec 2006)
  Release SQL Developer 1.2 (June 2007)
  Release SQL Developer 1.2.1 (Aug 2007)
  Release SQL Developer 1.5 (April 2008)

2.  Architecture and General Structure

Architecture Java IDE
  Connection Navigator
  Multiple Connections
  DB support (non-Oracle)
  Read read only support for Microsoft SQL Server, Microsoft Access, MySQL and Sybase
Globalization Translated UI (Translations for 9 languages planned for release 1.5.1)
  Asian / multi-byte
  Western European
Graphical User Interface Modern IDE Infrastructure
  Online Help
  HTML
  Shortcut Keys
Installation (Release 1.5) Footprint (38.4MB)
  Dependencies JRE (27MB)
  Full Download Size (77 MB)
  Does not require Oracle Home
Platform Support Windows (with Windows Installer )
  Linux (with RPM install)
  Mac OS/X
Database Object Search Searches across schema and object types
  Extended Search dialog in addition to DB Object search. This allows for searching for objects in code, such as parameters, declaration and usage.
Advanced Filtering Extended filtering:
  - Multiple filter options and criteria
  - Incorporates AND or OR filtering (All or Any selections)
  - Filtering on other users includes OBJECT_COUNT to filter out users with no visible objects
Search Engines Ask Tom
  Oracle doc
  Search Doc 10.2
  Search Doc 9.2
  Search Forums
  Search Google
  Search Metalink
Menus

Support for External Tools

  Export DDL and Data
  Database Objects Search
  Query Builder
Extensions

SQL Developer is built on an extensible IDE
  - Build full Java extensions
  - Use extension hooks for XML

Connection Navigator Run Manage Database report (right-click for context menu on any connection with privileges, such as SYS or SYSTEM)
  Run Manage Database report ( as SYS) to shut down and restart database
  Generate DB Doc (right-click for context menu on connections) for all objects in a schema. Open the generated index.html file in a browser to review objects.
Schema copy

To copy contents of one schema to another

  Choices include drop objects, truncate and copy
  Preview report of actions to be executed
  Log report of results
Schema Diff

Compare and create SQL Difference statement between 2 schemas

  Select objects to compare
  Report of objects to be compared
  List of differences and SQL Code to be executed
  Run script output to SQL Worksheet to be executed against connection of choice.

3.  Database Connections

Connections Create, save, update and rename connections
  Basic Thin JDBC
  Custom JDBC URL
  TNS Connections
  Connections via Connection Manager
  Passwords stored encrypted
  LDAP Connections
  External authentication
  Proxy connections
  Users prompted for new connection, when password expires
Third Party Access

Read-only connections to browse objects and data:
  - MySQL
  - SQL Server
  - MS Access
  - Sybase

TimesTen Integrated TimesTen support


4.  Connection Navigator

Browse Objects Allow sorting through column headings
 

View DDL

  View object definitions, e.g. dependencies, details, statistics
Tables

Create
  -Standard table
  -Partitioned
  -Index Organized
  -Global Temporary External
  -Simple Create Table
  -Advanced create
  -Generate PK Trigger and Sequence
  -Lob Parameters

 

Modify
  -Copy Table
  -Normalize Columns
  -Create, enable and disable constraints
  -Enable and disable related FK constraints

 

Table Data
  -Table Data Grid Edit
  -Duplicate row
  -Sort Data
  -Filter via SQL Where Clause
  -Single record view
  -Count rows
  -Export
  -Browse data (with XMLType columns)
  -View CLOB data

Views Create
  View Properties
  Modify
  Compile Invalid
  Update data in updatable views
Synonyms

Create  
   - Object based
   - Name based

  View Properties
  Modify
Sequences Create
  View Properties
  Modify
  Right-click context menu to alter sequence
Packages, Procedures,
Functions
Create
  View Properties
  Run
  View PL/SQL Arguments
  Compile (Individual selections, All or Invalid)
  Compile with Debug
  Default skeletal implementation from the spec for "Create Body"
  File based PL/SQL support
  - Open, edit and save pl/sql files
  - Compile pl/sql file against schema to update database
Types Create
  View Properties
  Modify
Triggers Create
  Edit Trigger Source
  Modify
  Triggers tab in master/detail format. Detail displays trigger code
Database Links Create
  View Properties
  Modify
  Test
Indexes

Create
  -Non-unique
  -Unique
  -Text Index
  -Bitmapped

  View Properties
Domain Indexes Create an index on any existing index type from any accessible schema
Messages Updated Error Display to show more specific to Oracle errors
  On Compile, display a success message in the status bar
Directory View Properties
Materialized Views Create
  View Properties
  Modify
  Advanced checkbox
  - Define the Select statement declaratively by choosing select items, from objects etc
  - Add indexes
  - Add partitions
  Add indexes for Materialized Views using "New Index"
Materialized View Logs New interface for creating and editing Materialized View Logs
Queues and Queue Tables Browse and view
Java Browse
  Load
Browse Other User Schemas  
Recycle Bin Purge
  Undrop Object
XML Schemas Create
  View Properties
Object Navigator Controls Filter/Find
  Refresh
  Download Grid Data to File
Users Create user with grants and roles etc
  Edit user roles, grants and privileges
Schema Diff Compare Source Schema and Objects with Destination schema to produce DDL
  Transfer SQL to SQL Worksheet
DBA Utilities Data Management
 

Database User Management
  -Create User
  -Alter User
  -Drop User

 

Generate DDL
  -DDL by Object
  -DDL by Schema

Application Express 3.0.1 Connect to and browse all Oracle APEX applications associated with a database schema (At application and page level)
  Export and Import Oracle APEX applications using a context menu

5.  SQL Worksheet

Data Grids Duplicate record button
  Single record "pivot" to view and edit one record at a time
  Allow columns to be reordered in SQL Worksheet and recall previous order
  Calendar prompt for date fields in table data editor
  Context Menu on grids
  Count Rows with filter
  Data grid navigation
  Auto resize column widths (on context menu on the data grid and column header)
  Copy any selection and paste it anywhere else in the grid
  Copy and paste the selection into Excel
  Copy a selection from Excel and paste into the data grid
Query Builder Create basic query
  Run query to test data
  Transfer SQL to SQL Worksheet
Snippets Drag and drop snippets onto SQL Worksheet
  Manage snippets
  - Create new categories
  - Create new snippets


SQL Worksheet Execute statement
  Execute script
  Switch connections for worksheet
 

File support
  - Browsing
  - Switch connection
  - Run
  - Edit
  - Save

  SQL statement formatting
  Bracket matching
  Support for displaying output of ref cursors
  BLOB, CLOB, xml, cursor support
  Code insight on
  - table alias
  - schema
  - partial table + ctrl +[space] (to come)
  - ctrl +[space] (to come)
  Describe table (F4)
  Refine tab names: Set worksheetname 'MyTab Name'
  SQL*Plus support (See list below)
  Pause long running script
  Resume paused script
  Select entire row by selecting rownum
  XQuery in the SQL Worksheet
  SQL History
  DBMS Output
  OWA Output
  Bind Variable
  Commit Options
  Autocommit Mode as a preference (not enabled by default )
  Code Formatting
  Export to File
  Cancel Query
  Updated code insight. Allows users to use code completion for a greater number of object types
  Replaced SQL Formatting. See SQL Formatter preferences to control white space, tabs, comma placement, case, new line etc.
  Code Templates for frequently used code. Create these in Preferences, then through key stroke, in Code Editors and SQL Worksheet, import code template . ctrl+shift+T)
  Code Templates are shareable : <user>/Application Data/Sql Developer/CodeTemplate.xml
  Flashback. Oracle 11g, you can use flashback on your data. See the Flashback tab on Tables.
  Keystroke to change case. (This is also available on a context menu.) Keystroke case is ctrl-quote
  Refactor code, see context menu on SQL Worksheet or Code Editors
  Separate unshared worksheet for connections for long running queries. Use the keystroke ctrl+shift+N. This will create a separate, unshared worksheet. The unshared worksheets are labeled as follows: HR_ _1
  SQL History stores commands form F5 and F9 keystrokes. i.e Execute script or Execute statement. Note if you run @filename, this is what is stored in the History, not every command from the file .Keystroke ctrl-up and ctrl-down adds and scrolls through previously executed commands in the SQL Worksheet.
  Drag and Drop from Navigator to Worksheet for Insert, Update, Delete, Join and Select statements. Set preference.
 

Data Grid on tables retains Sort.

  Data Grid filter retains drop list of previous filter options
Tuning Autotrace support
  Explain plan support
  Open *.trc file in SQL Developer for a formatted trace file.


6. SQL and PL/SQL Editing

Editing Bookmark OS Files
  Bookmarks for PL/SQL DB objects
  Code Folding
  Code Formatting
  Code Insight (code completion)
  Code Snippet Viewer
  Drag Objects into Editor
  File Operations (save, open)
  Inline Compilation Errors
  Standard Editor Operations
  Support File Open with Leading Remarks
  Syntax Highlighting
PL/SQL Debugger Inspect Row Types
  Set Break Points
Tuning Hierarchical Profiler (Available for connections to 11g R1 and above)

7. Exporting and Importing

Import Data Import data from spreadsheet (XLS format) into a table using SQL Developer
  Create table from xls spreadsheet import
  Import from CSV
Export Data Export data for more than one table at a time (with or without DDL)
  Export to  
  -CSV
  - XML
  - Text
  - HTML
  - XLS
  - SQL Insert Statements
  - SQL*Loader Syntax
Export DDL Export DDL allows the inclusion of drop objects
  INSERT data statement for multiple tables
  Option to suppress schema name
  Export full schema with single click
  Export to file, worksheet or clipboard from context menu


8.  Reporting

Supplied Reporting Database Session Browsing through master/ detail reports
  Export all reports
  Import all reports
  Increased right click actions
  Kill Session
  SQL Reporting
  Trace Session
  Export Report Results to Text, CSV, XLS, XML, HTML, INSERT, Loader
  ASH and AWR reports(Available for connections to Oracle 11g R1 and above, and for users licensed for the Oracle Diagnostic pack)
User Defined Reports Charting (select group,series,data from table)
  Copy shipped reports and paste as user defined reports
  Create and run chart report
  Create and run master detail reports
  Create a report using a script
  Create a report with plsql-dbms_output
e.g. begin dbms_output.put_line('<h1>hello</h1>'); end;
  Create a reports with formatted code (e.g. select text from user_source where rownum < 100)
  Create reports with gauges
  Click through user defined reports using Report Id
  Define prompt for bind variables
  Drill down links
  Creating a user defined report, allow user to browse and bring in a file to populate the SQL
  Import and export of user defined reports
  Share user defined reports
  Open user defined reports created in 1.0
Oracle Application Express Report Support for browsing Workspaces, applications, pages and schemas
Additional Reports Monitor Sessions report is also on the main Tools menu
  Manage Database report on the context menu of the connection in the navigator
Migration Reports Full set of reports to support third-party database migrations


9. SQL*Plus Support

All SQL and PL/SQL commands are supported as they are passed directly from the SQL Worksheet to the Oracle database. SQL*Plus commands used in the SQL Developer have to be interpreted by the SQL Worksheet before being passed to the database.

The SQL Worksheet currently supports a number of SQL*Plus commands. Those commands which are not supported by the SQL Worksheet are ignored and are not sent to the Oracle database. The SQL*Plus commands supported by Oracle SQL Developer SQL Worksheet are listed below.

Command

Comments

@


@@


acc[ept]


autotrace  
clear screen  

conn[ect]

Only valid in the context of Run Script. Commands in the script after CONNECT will be run against alternate schema but after completion of script, the SQL Worksheet is left within the context of the selected Connection.

def[ine]


desc[ribe]


doc[ument]


echo  
errors  
esc[ape]  

exec[ute]


exit

Stops execution and reinstates connection in combo box

feed[back]  
ho[st]  
pau[se]  

pro[mpt]


print  

quit

Stops execution and reinstates connection in combo box

rem[ark]


sta[rt]


term[out]  
timi[ng]  

undef[ine]


user  
var[iable]  
ver[ify]  

whenever


xquery  

10.  Third Party Database Support

Third Party Browsing Microsoft Access
  Microsoft SQL Server
  MySQL Database
  Sybase Adaptive Server
Migration Services Microsoft SQL Server to Oracle
  Microsoft Access to Oracle
  MySQL to Oracle
  Translate T-SQL to PL/SQL
  Translate Access to PL/SQL
  Sybase Adaptive Server to Oracle

 

11. Migration Workbench

Third-party databases supported Microsoft Access
  Microsoft SQL Server
  MySQL Database
  Sybase Adaptive Server
Connections Browse third-party databases
Migration Options Quick Migration Wizard offers least privilege migration i.e No DBA privilege is required.
  Step driven migration offers control at each stage of the migration process.
  Fine grain migration support provides users with the ability to select specific objects for migration.
  Complex object migration supports stored procedures, triggers and views
Translation Translation Scratch Editor offers single statement migration.
  Translation Difference Viewer provides side by side comparison by matching statements and blocks.

 

12.  Oracle Application Express (APEX) Support

Connections Navigator Create a database connection Oracle APEX database schemas
  Browse all Oracle APEX applications associated with a database schema, at application and page level
  Export and Import Oracle APEX applications using a context menu
Reporting Workspaces
  Applications
  Pages
  Schemas

13.  Preferences

Database Autotrace
  NLS
  Third party JDBC Driver
Environment Worksheet
  Object Viewer
  Open SQL worksheet on connect
  Auto-freeze/pin tab
  Allow fetch size to be greater than 5000
  SQL Array Fetch size
Code editor Bookmarks
  Insight
  Line Gutters
  Display settings
PL/SQL Debugger Use probe debugger to Debug PL/SQL
  Breakpoints properties
  Watches
  Inspector
User Defined Extensions Share user defined reports
  Add new context menus
  Create new tabs

14.  Version Control

Version Control Integration with CVS and Subversion
 

Versioning Navigator

  Create Subversion Repository
  Check out and commit files
  Branch/Tag
  Compare and merge
  Resolve conflicts
Files

File Navigator for file system file browsing

  View file history