Best PL/SQL Editor for Oracle Databases

Most often you interact with a database by writing and executing SQL queries, statements, stored procedures, and scripts. Therefore, it is clear that a good (or rather perfect) Oracle PL/SQL Code editor is a must tool for any developer. The tool is designed to enhance your editing capabilities and maximize the result.

While designing PL/SQL editor for dbForge Studio for Oracle we intended to create a tool to satisfy both professional developers and beginners. We tried to create an editor that would simplify, optimize and, above all, save your time while working with SQL documents. dbForge Studio for Oracle supplies SQL reporting tool that allows creating ad hoc data reports, sending them to their recipients, and automating the whole process.

  • Auto-complete the code with the necessary elements
  • Seize full control of your queries, scripts, and statements
  • Collapse the irrelevant parts of code to see the big picture
  • Select the type of formatting that fulfils your requirements
  • Navigate through large scripts and see the report structure

Getting started with SQL Editor

Let's start by identifying which steps one needs to take in order to begin working with SQL Editor. You can launch SQL editor in several ways. For instance:

  1. On the main menu, click File, point to New, and then click SQL.
  2. On the Standard toolbar, click New SQL.
  3. In Query Builder, switch to the Text View.
Oracle PL/SQL Editor

Statements coloring

SQL Editor integrated into dbForge Studio for Oracle has a number of features to boost your efficiency and improve code quality, one of them being statements highlighting. This gives you a considerable advantage as you can easily differentiate the colorized statements from the rest and read them with ease.

Statements Coloring

Context-sensitive code completion

SQL editor is designed to free the developer from remembering each and every minute code detail. By extracting the context-sensitive features from the code and the relations to other code elements, it can easily auto-complete the current code with the proper elements according to the pattern you choose.

Smart Code Completion

Code collapsing/expanding with support of user-defined regions

Whenever you have a "block" of code (BEGIN....END, etc.) SQL editor will put a little plus-sign in the margin and allow you to "collapse" that block of code so that you can only see the BEGIN and not the stuff underneath. If you're working with a large chunk of code, that's more than helpful.

Outlining Options

SQL formatting
with rich options

You can improve readability, consistency and standardization with SQL formatting. The feature enables to define and share SQL coding standards among multiple developers easily. Based on your needs, you can select from the 3 types of formatting:

  • Automatic code formatting significantly saves your time. By default, when you finish a statement with a delimiter, it is automatically formatted.
  • Manual. In case you want to format SQL code manually, decide whether you want to format the whole code in your script or only a statement.
  • Using SQL formatter wizard - to format multiple SQL files simultaneously.
Edit Formatting Profile

SQL snippets library with a browser window and editor

Code snippets are meant to help you type SQL code quickly. You can use predefined snippets provided in the application and create your own to insert them in SQL scripts and queries. Each snippet represents a statement or a group of statements fulfilling the same task such as creating a new user or describing a package. With SQL Editor, you can insert the required code snippet into your source code with a few mouse clicks.

SQL Code Snippets

Triggers in the SQL query editor

A trigger is a special kind of stored procedure that goes into effect when you modify data in a specified table using one or more data modification operations: UPDATE, INSERT or DELETE. Triggers can query other tables and can include complex SQL statements. They are primarily useful for enforcing complex business rules or requirements. For example, you could control whether to allow an order to be inserted based on a customer's current account status.

Triggers are also useful for enforcing referential integrity, which preserves the defined relationships between tables when you add, update, or delete the rows in those tables. However, the best way to enforce referential integrity is to define primary key and foreign key constraints in the related tables. If you use database diagrams, you can create a relationship between tables to automatically create a foreign key constraint.

Create Trigger

Document Outline window for navigation through large scripts

The Document Outline window provides quick and convenient navigation through the report elements. You can use it while building a report to access all report elements and to see the whole report structure.

Document Outline

Execution of scripts, selected SQL fragments, and statements

You can have full control of scripts and statements right from the editor. Create, edit, execute, and terminate scripts in a wink. Oracle SQL Editor:

  • Provides you with rich editing and debugging experience when you are working with scripts.
  • Supports IntelliSense as well as color-coding of syntax to improve the readability of complex statements.
  • Supports Find and Replace, bulk commenting, custom fonts and colors, and line numbering.
  • Allows you to change the database against which the script will be executed.
Execute Script

One-step access to a schema object editor from code

Getting started with SQL Editor

Let's start by identifying which steps one needs to take in order to begin working with SQL Editor. You can launch SQL editor in several ways. For instance:

  1. On the main menu, click File, point to New, and then click SQL.
  2. On the Standard toolbar, click New SQL.
  3. In Query Builder, switch to the Text View.

This option allows you to open Visual Object Editor and explore DDL objects with their properties. If the definition is outside of the current script, an object editor or a corresponding DDL script will open in another window. You will appreciate this feature even more while working with SQL scripts in a database project. A project can contain SQL scripts and query files located in different folders and on different discs, but, with the Go to definition option, you still can navigate to the definitions of database objects from the scripts where these objects are mentioned.

Got To Definition

Parameter information for stored procedures and functions

The feature will help you quickly see the names and types of parameters that are used in a function or stored procedure script. All information is neatly displayed in the Parameter Info tooltip. The parameter in bold is the next parameter that is required for the function or stored procedure.

Parameter Information

Quick access to favorite templates from SQL editor

If you have the same code snippet in multiple reports, it is a good idea to use query templates. They allow you to define reusable snippets of SQL components to be used across different reports. Besides, code completion is significantly simplified with this comprehensive set of templates as they are called in one click.

Query Templates

Quick information about schema objects

The Quick Info feature is a tooltip with brief information about database objects, parameters, and variables. The tooltip appears when you place the mouse pointer on the required item in the SQL script or press the shortcut Ctrl+K and then I. In the case of database objects, the tooltip shows their location and types. You will find this option useful while working with large scripts.

Quick Information about Schema Objects

SQL Execution history window for the document

SQL statement history is designed to ease recalling and re-executing statements you have previously executed. Choose View SQL history from the SQL menu to bring up a dialog where you can choose which SQL statement must be inserted into the editor instead of the current one. Point with the mouse on a statement in history to view its full text in a hint.

Execution History

Conclusion

SQL Editor embedded into dbForge Studio for Oracle is exactly the tool you need to deal with all kinds of issues, ranging from SQL formatting to extracting the necessary information about Oracle database objects. With its help, you can greatly boost your productivity and increase the speed of writing and executing queries. You can also ensure your code is correct by using a rich collection of code snippets and query templates. Besides, you get to easily access object definitions and find information about schema objects. Make smart use of your time with our superb Oracle database Editor!

dbForge Studio for Oracle

Powerful development environment for Oracle