There are numerous products available to programmers and those who want to manage database solutions might appreciate the functions of Oracle SQL Developer.
It is a so-called IDE (Integrated Development Environment) meant to cut down the hassle when it comes to the development or management of Oracle databases. Before installing it, users need to make sure they have Java installed onto the host computer or the setup process will fail.
Not only does the software solution support editing and debugging of PL/SQL applications, but it also comes with a platform for deploying SQL scripts or queries.
Due to the app’s integrated DBA console, developers can easily administer the database contents, generate reports or migrate third-party databases to Oracle.
The interface is neatly organized within several sections, so that each action or query can be performed without needing to access additional windows or menus. Thus, the left panel can be used to find and select objects, while the right section provides details on the selected items.
In addition, Oracle SQL Developer can be used to connect to non-Oracle databases with as little effort as possible, so users can explore data stored in Microsoft SQL Server, Access, MySQL, Sybase Adaptive Server, and IBM DB2 databases.
Other functions provided by Oracle SQL Developer are the creation or import of database designs, along with the building and editing of Data Modeler objects.
The component palette can also be adjusted, yet the options are quite limited for certain page types and existing components.
Oracle SQL Developer Features:
- Object Browser:
SQL Developer allows you to explore the contents of your database using the connection tree. Expanding the tables node will show you the tables for the connected user. A complete list of supported database object types follows.
It contains Tables, Views, Editioning Views, Indexes, Packages, Procedures, Functions, Queues, Queue Tables, Triggers, Crossedition Triggers, Types, Sequences, Materialized Views, Materialized View, Logs, Synonyms, Public Synonyms, Database Links, Public Database Links, ,Application Express, Java, XML Schemas, XML DB Repository, Analytic Workspaces, Scheduler (DBMS_SCHEDULER & DBMS_JOBS).
Browsing the contents outside the current schema can be accomplished by expanding the ‘Other Users’ tree node or by using the Schema Browser (available on the connection context menu.)
2. SQL Worksheet
The SQL Worksheet is an editor that allows for the execution of SQL statements, scripts, and PL/SQL anonymous blocks. SELECT statements can be executed to return results in a spreadsheet-like ‘grid’ or can be executed as a script such to emulate SQL*Plus behavior and output.
Basic SQL Worksheet functionality includes:
- SQL statement and script execution recall
- SQL formatting
- Explain Plan
- SQL Tuning Advisor – Requires Tuning Pack License
- Code Intellisense – Type Ahead – Completion
- Visual Query Builder
- Integrated File History and Change Logging
Selected objects will open in an editor allowing the user to view or manipulate it based on their granted privileges.
3. Data Grids:
The grids present data in a spreadsheet, row:col like interface. These are used throughout the product, but primarily for displaying the contents of tables and the results from executing one or more queries.
Basic SQL Worksheet functionality includes:
- Single Record View
- Export to popular formats including:
- Insert statements
- Advanced data type viewers for BLOBs, XML, Dates, etc.
- Filtering and multi-column sorting
- Custom display for NULL values
- Search and highlight
4. Procedure Editor
The Procedure Editor is a complete IDE for the PL/SQL programming language. Stored Procedures, Functions, Packages, Types, and Triggers can be executed, compiled, refactored and more using the Procedure Editor. Access the Procedure Editor by clicking on a PL/SQL object in the connection tree or by opening a file with a file extension mapped to PL/SQL objects.
Basic Procedure Editor functionality includes:
- Compile with Debug
- Error message feedback and linking to offending source
- Automatic Capture of Execution Output including Ref Cursors
- Remote Debug
- Hierarchical Profiling
- Documentation via DB Doc
- Code Templates
5. Database Utilities
From the Tools menu, users can access several powerful and wizard-driven mechanisms for performing routine database tasks, including:
- Database Copy – copy one or more objects to another database
- Database Diff – compare objects across schemas or databases and generate ALTER scripts and reports
- Database Export – export one or more objects to Excel, CSV, etc.
- Database Import – available from Tables node or table context menu, import data from Excel, text, etc. directly to existing or new table
- Monitor Sessions – manage database sessions, i.e. kill, trace, and browse
- Monitor SQL – an interface to the Real Time SQL Monitoring feature, requires the Tuning Pack
6. Oracle Database Migrations
Oracle SQL Developer is the primary migration platform for Oracle Database. Move your Teradata, DB2, Sybase, SQL Server, MySQL, and Access databases and applications to Oracle with SQL Developer.
- Connect, query, and browse 3rd party databases
- Wizard Driven Interface
- Project Management and Reports
- Map datatypes between systems
- Translate your stored procedures and SQL statements to Oracle
- Deploy Oracle Database 12c SQL Translation Framework Translators and manage your Translation Profiles
- Scan your application source code for SQL that requires translation
The main purpose of Oracle SQL Developer is to simplify the process of database management for all those who work with Oracle data. However, only advanced users who experiment with the application, as it might come with a steep learning curve for the novices.